Re: [PHP-DB] Query does not work

2014-07-02 Thread Lester Caine
On 02/07/14 04:43, Ethan Rosenberg, PhD wrote:
 while ($row31 = mysqli_fetch_row($result31)) {
   printf (%s %s %s %s %s\n, $row31[0], $row31[1], $row31[2], $row31[3]);

Try
print_r( $row31 );

 } // no output
 
 How can I loose a db connection in the middle of a program?
This is not showing that you have, only that you can't see the actual
data returned.

I prefer firebird to mysql, and the normal process is to use the
mysqli_fetch_assoc style working so that the keys of the array are the
returned field names in which case the print_r output gives more
information ...

-- 
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query does not work

2014-07-02 Thread Jim Giner
Once again you have provided the group with RANDOM pieces of code, 
completely out of context since you have already shown me that your 
query and db connection are being used in a function, hence your loss of 
$cxn.


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query will not work - SOLVED

2013-02-02 Thread Karl DeSaulniers
$sql13 = UPDATE `Customers` SET `Lname` = 'Barnet',  `City` =  
'Lakewood',  `State` = 'NJ' WHERE `Cust_Num` = 1089;


$result13 = mysqli_query($cxn, $sql13);

if(mysqli_num_rows($result13) 0)
{
  $row_cnt = mysqli_num_rows($result13);
  echo row count result13 is $row_cntbr /;
}

else
{
echo Ouch result13br /;
}

mysqli_free_result($result13);

$sql1 = select * FROM `Customers` ORDER BY `Cust_Num`;

$result1 = mysqli_query($cxn, $sql1);

if(mysqli_num_rows($result1) 0)
{
$row_cnt = mysqli_num_rows($result1);
echo Row count result1 is $row_cntbr /;
}

else
{
echo Ouch result1br /;
}

Try that..

Best,
Karl


On Feb 2, 2013, at 8:08 PM, Ethan Rosenberg, PhD wrote:


I  must be missing something fundamental!!

Here is a code snippet:

$sql13 =  UPDATE Customers SET Lname = 'Barnet',  City =  
'Lakewood',  State = 'NJ' WHERE Cust_Num = 1089;


$result13 = mysqli_query($cxn, UPDATE Customers SET Lname =  
'Bleich',  City = 'Lakewood',  State = 'NJ' WHERE Cust_Num = 1089);


if($row_cnt = mysqli_num_rows($result13)!= 0)
{
  $row_cnt = mysqli_num_rows($result13);
  echo row count result13 is $row_cntbr /;
}

else
{
echo Ouch result13br /;
}

mysqli_free_result($result13);

$sql1 = select Cust_Num, Fname, Lname, Street, City, State, Zip,  
Phone, Notes from Customers order by Cust_Num;


$result1 = mysqli_query($cxn, $sql1);

if($row_cnt = mysqli_num_rows($result1)!= 0)
{
$row_cnt = mysqli_num_rows($result1);
echo Row count result1 is $row_cntbr /;
}

else
{
echo Ouch result1br /;
}

Here are my results:


Ouch result13

Row count result1 is 45

What am I doing wrong??

Ethan
=


I was using the worker@localhost, which did not have the Update  
privilege. Added the privilege that, and everything worked.


Sorry for bothering you with trivia.

Ethan



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Karl DeSaulniers
Design Drumm
http://designdrumm.com


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query will not work - SOLVED

2013-02-02 Thread tamouse mailing lists
On Sat, Feb 2, 2013 at 8:08 PM, Ethan Rosenberg, PhD
erosenb...@hygeiabiomedical.com wrote:
 I  must be missing something fundamental!!

Yes.

 I was using the worker@localhost, which did not have the Update privilege.
 Added the privilege that, and everything worked.

Please explain why you are not checking error returns from function calls?

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query Question

2011-05-23 Thread maarten
I would keep the db names and/or schema names in your queries.

It clarifies what you are doing with the query making it easier for
someone else to debug, improve, ...  Certainly for those not familiar
with your db structure.

I am not aware of any drawbacks that can result from this.  (Ok, maybe
your program is a few bytes longer?)

regards,
Maarten

On Sun, 2011-05-22 at 05:27 -0400, ad...@buskirkgraphics.com wrote:
 I have been working on a class methods for some time now.
 
  
 
 I have reached a cross road when it comes to common practice of developing
 query structure.
 
  
 
 Long ago I wrote queries where I  just called the field I wanted on a
 particular table unless I was joining them.
 
  
 
 Example:
 
 $query =  SELECT id FROM Table WHERE Clause;   
 
  
 
 Through time I developed a habit of queering as such.
 
 Example:
 
 $query = SELECT tablename.id FROM db.table WHERE clause;
 
  
 
 
 
 I have felt that, because my server contains multiple databases and I needed
 to jump between databases and tables without changing the connector this
 always has been best practice for me.
 
  
 
 Someone recently told me,
 
 Rich, 
 
 I do not agree with your design of the queries.
 
 There is no need to include the DB and table name in the query if you are
 not joining tables.
 
  
 
 
 
 While I have a very hard time understanding this response as being valid. I
 will propose the question. 
 
  
 
 
 
 Is it bad practice to write queries with the database and table name in the
 queries even if I am NOT joining tables?
 
 Is there an impact from PHP or MySQL that is caused by doing so?
 
  
 
 I know this more a MySQL question but as PHP developers we all deal with
 queries on a day to day bases, 
 
 and when developing more flexible class methods I build the queries in the
 method.
 
  
 
 
 
 
 
 
 
 
 
 Richard L. Buskirk
 

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query Question

2011-05-23 Thread maarten
That's a very good point,

but since I use postgres that's one point that doesn't affect me and as
such didn't cross my mind at the time.

(Postgres uses schemas where mysql uses databases, a different database
on postgres usually means a different database machine.  And joining
tables from different database machines isn't really plausible.)

I read the question as 'schema names' in stead of 'database names'.


On Mon, 2011-05-23 at 06:47 -0500, Bret Hughes wrote:
 I like to set the dbname in a config file and use it only for making the 
 connection to the database.  That way I can test/develop against 
 different databases on the same server.  By locking yourself into 
 dbnames in the queries themselves I believe you loose a huge amount of 
 environmental flexibility.
 
 Using tablenames and or aliases is something I do a fair amount of but 
 only when joining or with complicated queries where clarity is an issue.
 
 On 05/23/2011 02:20 AM, maarten wrote:
  I would keep the db names and/or schema names in your queries.
 
  It clarifies what you are doing with the query making it easier for
  someone else to debug, improve, ...  Certainly for those not familiar
  with your db structure.
 
  I am not aware of any drawbacks that can result from this.  (Ok, maybe
  your program is a few bytes longer?)
 
  regards,
  Maarten
 
  On Sun, 2011-05-22 at 05:27 -0400, ad...@buskirkgraphics.com wrote:
  I have been working on a class methods for some time now.
 
 
 
  I have reached a cross road when it comes to common practice of developing
  query structure.
 
 
 
  Long ago I wrote queries where I  just called the field I wanted on a
  particular table unless I was joining them.
 
 
 
  Example:
 
  $query =  SELECT id FROM Table WHERE Clause;
 
 
 
  Through time I developed a habit of queering as such.
 
  Example:
 
  $query = SELECT tablename.id FROM db.table WHERE clause;
 
 
 
 
 
  I have felt that, because my server contains multiple databases and I 
  needed
  to jump between databases and tables without changing the connector this
  always has been best practice for me.
 
 
 
  Someone recently told me,
 
   Rich,
 
  I do not agree with your design of the queries.
 
  There is no need to include the DB and table name in the query if you are
  not joining tables.
 
 
 
 
 
  While I have a very hard time understanding this response as being valid. I
  will propose the question.
 
 
 
 
 
  Is it bad practice to write queries with the database and table name in the
  queries even if I am NOT joining tables?
 
  Is there an impact from PHP or MySQL that is caused by doing so?
 
 
 
  I know this more a MySQL question but as PHP developers we all deal with
  queries on a day to day bases,
 
  and when developing more flexible class methods I build the queries in the
  method.
 
 
 
 
 
 
 
 
 
 
 
  Richard L. Buskirk
 

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query syntax error?

2011-01-13 Thread jose
you have renamed places_data table to 'a'

2011/1/13 Harvey har...@harveyk.com:
 Hello,

 I have a query on a page that used to work fine, but is now generating an
 error.

 I assume that the version of php or mysql was updated on the webhost server
 or something like that?

 Here is the query:

 select count(places_data.place_id) as areacount, boroughs.borough_name as
 boroname, area.area_name as areaname, area.area_id as areaid
 from places_data a
 inner join boroughs b on a.area_fid = b.area_id
 inner join area c on c.borough_fid = b.borough_id
 where places_data.on_off_fid = 2
  . $cat_query . 
 group by c.area_name
 order by b.borough_name ASC, c.area_name ASC

 And here is the error message:

 Unknown column 'places_data.place_id' in 'field list'

 But place_id is a field in places_data table.

 Any ideas?

 Thanks!

 Harvey


 --
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



RE: [PHP-DB] Query syntax error?

2011-01-13 Thread Harvey
Thanks, I got it working now, had to use the a/b/c thing a few times


-Original Message-
From: jose [mailto:jojap...@gmail.com] 
Sent: Thursday, January 13, 2011 9:52 AM
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] Query syntax error?

you have renamed places_data table to 'a'

2011/1/13 Harvey har...@harveyk.com:
 Hello,

 I have a query on a page that used to work fine, but is now generating an
 error.

 I assume that the version of php or mysql was updated on the webhost
server
 or something like that?

 Here is the query:

 select count(places_data.place_id) as areacount, boroughs.borough_name as
 boroname, area.area_name as areaname, area.area_id as areaid
 from places_data a
 inner join boroughs b on a.area_fid = b.area_id
 inner join area c on c.borough_fid = b.borough_id
 where places_data.on_off_fid = 2
  . $cat_query . 
 group by c.area_name
 order by b.borough_name ASC, c.area_name ASC

 And here is the error message:

 Unknown column 'places_data.place_id' in 'field list'

 But place_id is a field in places_data table.

 Any ideas?

 Thanks!

 Harvey


 --
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] query help

2010-11-17 Thread Artur Ejsmont
well  i guess you could do that. but it gets complicated after a
while and there will be a lot of work and probably after a while you
will get into some problems. You have to handle escaping, special
types etc. Then what about performance? how to query the data ...
using similar approach or is it just for inserts? there is a bit of
work to make it really usable i guess.

I am not saying its wrong though, I have seen this approach twice ...
in general its possible.

Maybe better choice would be to try to use some orm ? there are plenty
of frameworks out there  the only problem is the learning curve
may be steep.

What others think?

art

On 17 November 2010 13:51, Vinay Kannan viny...@gmail.com wrote:
 Hello PHP Gurus,

 I need your help on an insert query.

 I wanted to know if there is way to insert an array of values into a DB. An
 eg would explain this better :

 If I have 2 tables in a DB, 1) users has 3 columns 2) hobbies = 5 columns

 I was thinking of having a single function which will perform the insert on
 any  insert which happens on the entire website.

 Eg : This function can be called with 2 parameters, the first parameter the
 table name, and the second parameter is an array of values which will be
 inserted into the table.
 eg : Users has these columns [1]ID [2] Name [3]Location
 so the function call would be something like *
 insert_into_tbale(users,array[user_values])*
 **
 Does this make sense ? Is this a good method to follow ?

 Thanks in advance !

 Vinay Kannan.




-- 
Visit me at:
http://artur.ejsmont.org/blog/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] query help

2010-11-17 Thread Bastien Koert
On Wed, Nov 17, 2010 at 8:51 AM, Vinay Kannan viny...@gmail.com wrote:
 Hello PHP Gurus,

 I need your help on an insert query.

 I wanted to know if there is way to insert an array of values into a DB. An
 eg would explain this better :

 If I have 2 tables in a DB, 1) users has 3 columns 2) hobbies = 5 columns

 I was thinking of having a single function which will perform the insert on
 any  insert which happens on the entire website.

 Eg : This function can be called with 2 parameters, the first parameter the
 table name, and the second parameter is an array of values which will be
 inserted into the table.
 eg : Users has these columns [1]ID [2] Name [3]Location
 so the function call would be something like *
 insert_into_tbale(users,array[user_values])*
 **
 Does this make sense ? Is this a good method to follow ?

 Thanks in advance !

 Vinay Kannan.


codeigniter works this way. But they have a lot of extra functionality
to keep the data safe for inserts. Check it out.

-- 

Bastien

Cat, the other other white meat

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] query help

2010-11-17 Thread Max E.K
From: Vinay Kannan viny...@gmail.com
To: PHP DB php-db@lists.php.net, php mysql 
php_mysql_usergr...@yahoogroups.com
Sent: Wednesday, November 17, 2010 2:51:35 PM GMT +01:00 Amsterdam / Berlin / 
Bern / Rome / Stockholm / Vienna
Subject: [PHP-DB] query help

Hello PHP Gurus,

I need your help on an insert query.

I wanted to know if there is way to insert an array of values into a DB. An
eg would explain this better :

If I have 2 tables in a DB, 1) users has 3 columns 2) hobbies = 5 columns

I was thinking of having a single function which will perform the insert on
any  insert which happens on the entire website.

Eg : This function can be called with 2 parameters, the first parameter the
table name, and the second parameter is an array of values which will be
inserted into the table.
eg : Users has these columns [1]ID [2] Name [3]Location
so the function call would be something like *
insert_into_tbale(users,array[user_values])*
**
Does this make sense ? Is this a good method to follow ?

Thanks in advance !

Vinay Kannan.




Hi Vinay,

You may want to try codeigniter.

More info here.

http://codeigniter.com/user_guide/database/active_record.html#insert


with kind regards,

Max.

Max Kimambo
Franz-Stenzer-Straße, 51
12679, Berlin.
T: +493057706550 (new number)
M: +4917649520175


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] query help

2010-11-17 Thread Niel Archer
 Hello PHP Gurus,
 
 I need your help on an insert query.
 
 I wanted to know if there is way to insert an array of values into a DB. An
 eg would explain this better :
 
 If I have 2 tables in a DB, 1) users has 3 columns 2) hobbies = 5 columns
 
 I was thinking of having a single function which will perform the insert on
 any  insert which happens on the entire website.
 
 Eg : This function can be called with 2 parameters, the first parameter the
 table name, and the second parameter is an array of values which will be
 inserted into the table.
 eg : Users has these columns [1]ID [2] Name [3]Location
 so the function call would be something like *
 insert_into_tbale(users,array[user_values])*
 **
 Does this make sense ? Is this a good method to follow ?
 
 Thanks in advance !
 
 Vinay Kannan.

You don't give any info about the database engine, but assuming you're
using MySQL  take a look at
http://dev.mysql.com/doc/refman/5.0/en/insert.html
Specifically you can use your idea to build an INSERT/VALUE version of
the syntax

INSERT INTO table (col1, col2, .colN.)  VALUES (col1Value1, col2value1,
colNvalue1),  (col1Value2, col2value2, colNvalue2),  ...


--
Niel Archer
niel.archer (at) blueyonder.co.uk


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



RE: [PHP-DB] query help

2010-11-17 Thread Constantin Brinzoi

Hey,

You can also try PEAR module MDB2 to insert an array into a table. The
function is: executeMultiple. This works for other SQL queries too.

Check this link please:
http://pear.php.net/manual/en/package.database.mdb2.intro-execute.php

Cheers,


-Original Message-
From: Artur Ejsmont [mailto:ejsmont.ar...@gmail.com] 
Sent: 17 noiembrie 2010 16:07
To: Vinay Kannan
Cc: PHP DB; php mysql
Subject: Re: [PHP-DB] query help

well  i guess you could do that. but it gets complicated after a while
and there will be a lot of work and probably after a while you will get into
some problems. You have to handle escaping, special types etc. Then what
about performance? how to query the data ...
using similar approach or is it just for inserts? there is a bit of work to
make it really usable i guess.

I am not saying its wrong though, I have seen this approach twice ...
in general its possible.

Maybe better choice would be to try to use some orm ? there are plenty of
frameworks out there  the only problem is the learning curve may be
steep.

What others think?

art

On 17 November 2010 13:51, Vinay Kannan viny...@gmail.com wrote:
 Hello PHP Gurus,

 I need your help on an insert query.

 I wanted to know if there is way to insert an array of values into a 
 DB. An eg would explain this better :

 If I have 2 tables in a DB, 1) users has 3 columns 2) hobbies = 5 
 columns

 I was thinking of having a single function which will perform the 
 insert on any  insert which happens on the entire website.

 Eg : This function can be called with 2 parameters, the first 
 parameter the table name, and the second parameter is an array of 
 values which will be inserted into the table.
 eg : Users has these columns [1]ID [2] Name [3]Location so the 
 function call would be something like *
 insert_into_tbale(users,array[user_values])*
 **
 Does this make sense ? Is this a good method to follow ?

 Thanks in advance !

 Vinay Kannan.




--
Visit me at:
http://artur.ejsmont.org/blog/

--
PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit:
http://www.php.net/unsub.php


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



RE: [PHP-DB] query help

2010-11-17 Thread Ashay Chaudhary
The most performant methods are to use well parameterized stored procedure or a 
prepared parameterized statement.

: Ashay

-Original Message-
From: Niel Archer [mailto:n...@chance.now] 
Sent: Wednesday, November 17, 2010 6:30 AM
To: php-db@lists.php.net
Subject: Re: [PHP-DB] query help

 Hello PHP Gurus,
 
 I need your help on an insert query.
 
 I wanted to know if there is way to insert an array of values into a 
 DB. An eg would explain this better :
 
 If I have 2 tables in a DB, 1) users has 3 columns 2) hobbies = 5 
 columns
 
 I was thinking of having a single function which will perform the 
 insert on any  insert which happens on the entire website.
 
 Eg : This function can be called with 2 parameters, the first 
 parameter the table name, and the second parameter is an array of 
 values which will be inserted into the table.
 eg : Users has these columns [1]ID [2] Name [3]Location so the 
 function call would be something like *
 insert_into_tbale(users,array[user_values])*
 **
 Does this make sense ? Is this a good method to follow ?
 
 Thanks in advance !
 
 Vinay Kannan.

You don't give any info about the database engine, but assuming you're using 
MySQL  take a look at http://dev.mysql.com/doc/refman/5.0/en/insert.html
Specifically you can use your idea to build an INSERT/VALUE version of the 
syntax

INSERT INTO table (col1, col2, .colN.)  VALUES (col1Value1, col2value1, 
colNvalue1),  (col1Value2, col2value2, colNvalue2),  ...


--
Niel Archer
niel.archer (at) blueyonder.co.uk


--
PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: 
http://www.php.net/unsub.php



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query for duplicate records

2010-10-17 Thread Chris

On 18/10/10 06:55, Ron Piggott wrote:

Is there a query you could help me write a SELECT query that would search table 
`ministry_profiles` for where column `organization`  has the same organization 
more than once?  I am trying to delete the duplicate organization records, but 
I am working with 1,000+ businesses and I can't go through each record looking 
for duplicates.


select organization, count(*) from ministry_profiles group by 
organization having count(*)  1;


gives you which organization has more than 1 account (and how many 
duplicates you are dealing with).


--
Postgresql  php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query stopping after 2 records?

2009-04-27 Thread Bastien Koert
On Mon, Apr 27, 2009 at 10:19 AM, Miller, Terion 
tmil...@springfi.gannett.com wrote:

 I need help/advice figuring out why my query dies after 2 records.  Here is
 the query:

 // Build your INSERT statement here


  $query = INSERT into `warrants` (wid, name, age, warrant, bond, wnumber,
 crime) VALUES (;
$query .=  '$wid', '$name', '$age', '$warrant',
 '$bond', '$wnumber', '$crime' );
$wid = mysql_insert_id();

 // run query

  mysql_query($query) or die (GRRR);


  echo $query;

 It inserts two records and dies half way thru the 3rd?
 Thanks in advance for clues to fix this.
 T.Miller


 --
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php


echo out each sql statement and then change the or die to

mysql_query($query) or die (mysql_error());
-- 

Bastien

Cat, the other other white meat


Re: [PHP-DB] Query stopping after 2 records?

2009-04-27 Thread Hawx

On Mon, 27 Apr 2009 07:19:23 -0700, Miller, Terion
tmil...@springfi.gannett.com wrote:
 I need help/advice figuring out why my query dies after 2 records.  Here
is
 the query:
 
  // Build your INSERT statement here
   
 
  $query = INSERT into `warrants` (wid, name, age, warrant, bond,
wnumber,
 crime) VALUES (;
 $query .=  '$wid', '$name', '$age', '$warrant',
 '$bond', '$wnumber', '$crime' );
 $wid = mysql_insert_id();
 
 // run query
 
  mysql_query($query) or die (GRRR);
 
 
   echo $query;
 
 It inserts two records and dies half way thru the 3rd?
 Thanks in advance for clues to fix this.
 T.Miller

Make sure any apostrophes in the variable strings are escaped correctly.
Check the addslashes() function.

-- 
Hawx

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query stopping after 2 records?

2009-04-27 Thread Chris

Hawx wrote:

On Mon, 27 Apr 2009 07:19:23 -0700, Miller, Terion
tmil...@springfi.gannett.com wrote:

I need help/advice figuring out why my query dies after 2 records.  Here

is

the query:

 // Build your INSERT statement here
  


 $query = INSERT into `warrants` (wid, name, age, warrant, bond,

wnumber,

crime) VALUES (;
$query .=  '$wid', '$name', '$age', '$warrant',
'$bond', '$wnumber', '$crime' );
$wid = mysql_insert_id();

// run query

 mysql_query($query) or die (GRRR);



  echo $query;

It inserts two records and dies half way thru the 3rd?
Thanks in advance for clues to fix this.
T.Miller


Make sure any apostrophes in the variable strings are escaped correctly.
Check the addslashes() function.


No - use the mysql_real_escape_string function. Addslashes is the wrong 
thing to use here.


--
Postgresql  php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] query optimization

2008-09-28 Thread Chris

Yves Sucaet wrote:

Hi Jack,

I'm expecting less than 10 records in the resulting set.
The BlockUnit table contains 337,253 records; the InteractionParts table 
contains 279,953 records.

It takes currently 8.3 seconds to execute the query as I have it.

Erh, this is embarassing but I'm going to need some help re-writing it with 
EXISTS...

Thanks for the help so far,


You haven't said which parts of the query is slow.

Try:

Just the subselect query.
If that's slow, try just the first part of the union.
If that's not slow, try the second part.

At least you'll know where to concentrate.

--
Postgresql  php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] query optimization

2008-09-26 Thread Yves Sucaet
Hi Jack,

I'm expecting less than 10 records in the resulting set.
The BlockUnit table contains 337,253 records; the InteractionParts table 
contains 279,953 records.

It takes currently 8.3 seconds to execute the query as I have it.

Erh, this is embarassing but I'm going to need some help re-writing it with 
EXISTS...

Thanks for the help so far,

Yves

  - Original Message - 
  From: Jack van Zanen 
  To: Chris 
  Cc: YVES SUCAET ; php-db@lists.php.net 
  Sent: Thursday, September 25, 2008 7:49 PM
  Subject: Re: [PHP-DB] query optimization


  If you can answer the other questions that would help as well

  you can try rewriting using exist instead of in

  But without the basic information  like number of records expected and 
explain plan it is very hard to come up with a better solution.


  Brgds

  Jack


  2008/9/26 Chris [EMAIL PROTECTED]

Jack van Zanen wrote:

  Hi

  If I am not mistaken,
  the second part of the union contains all rows that are in the first part 
of
  the union. just remove the first part.



Kind of.

The first part is a join, the second isn't.

I was going to suggest rewriting the subquery into a single:

where
ip.blockid in (...)
or
bu.blockid in (...)

however that'll probably be slower, but def. worth a try. 


-- 
Postgresql  php tutorials
http://www.designmagick.com/





  -- 
  J.A. van Zanen


Re: [PHP-DB] query optimization - DB

2008-09-26 Thread Yves Sucaet

Oh, sorry I forgot to mention this. It's a MySQL database.

- Original Message - 
From: Micah Gersten [EMAIL PROTECTED]

To: YVES SUCAET [EMAIL PROTECTED]
Cc: php-db@lists.php.net
Sent: Thursday, September 25, 2008 7:55 PM
Subject: Re: [PHP-DB] query optimization



Other question is, what DB is this for?

Thank you,
Micah Gersten
onShore Networks
Internal Developer
http://www.onshore.com



YVES SUCAET wrote:

How could I rewrite the following query so it runs faster:

select distinct location from blockunit where blockid in (
  select bu.blockid from blockunit bu inner join interactionparts ip on
(bu.blockid = ip.part)
  where ip.blockid in

(110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087,
  124088,124562,124563,133358,133359,133409,133410,135304,135305,136096)
  union
  select bu.blockid from blockunit bu
  where bu.blockid in

(110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087,
  124088,124562,124563,133358,133359,133409,133410,135304,135305,136096)
)

Thanks in advance,

Yves










--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] query optimization - DB

2008-09-26 Thread Micah Gersten
MySQL queries use 1 index per table, so to speed the query, we need to
know what indices you have for the 2 tables.

Thank you,
Micah Gersten
onShore Networks
Internal Developer
http://www.onshore.com



Yves Sucaet wrote:
 Oh, sorry I forgot to mention this. It's a MySQL database.

 - Original Message - From: Micah Gersten [EMAIL PROTECTED]
 To: YVES SUCAET [EMAIL PROTECTED]
 Cc: php-db@lists.php.net
 Sent: Thursday, September 25, 2008 7:55 PM
 Subject: Re: [PHP-DB] query optimization


 Other question is, what DB is this for?

 Thank you,
 Micah Gersten
 onShore Networks
 Internal Developer
 http://www.onshore.com



 YVES SUCAET wrote:
 How could I rewrite the following query so it runs faster:

 select distinct location from blockunit where blockid in (
   select bu.blockid from blockunit bu inner join interactionparts ip on
 (bu.blockid = ip.part)
   where ip.blockid in

 (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087,

  
 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096)
   union
   select bu.blockid from blockunit bu
   where bu.blockid in

 (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087,

  
 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096)
 )

 Thanks in advance,

 Yves









-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] query optimization - DB

2008-09-26 Thread Yves Sucaet

Hi Micah,

I'm learning here. Great! :-)

How can I look this up? I'm pretty sure multiple fields are indexed. So 
should I specify explicitely which indices should be used? What fields do 
you think should be indexed? I do have control over the database and can 
create additional indices.


Can you help out rewriting the query using EXISTS syntax?

Thanks in advance,

Yves

- Original Message - 
From: Micah Gersten [EMAIL PROTECTED]

To: php-db@lists.php.net
Sent: Friday, September 26, 2008 11:47 AM
Subject: Re: [PHP-DB] query optimization - DB



MySQL queries use 1 index per table, so to speed the query, we need to
know what indices you have for the 2 tables.

Thank you,
Micah Gersten
onShore Networks
Internal Developer
http://www.onshore.com



Yves Sucaet wrote:

Oh, sorry I forgot to mention this. It's a MySQL database.

- Original Message - From: Micah Gersten [EMAIL PROTECTED]
To: YVES SUCAET [EMAIL PROTECTED]
Cc: php-db@lists.php.net
Sent: Thursday, September 25, 2008 7:55 PM
Subject: Re: [PHP-DB] query optimization



Other question is, what DB is this for?

Thank you,
Micah Gersten
onShore Networks
Internal Developer
http://www.onshore.com



YVES SUCAET wrote:

How could I rewrite the following query so it runs faster:

select distinct location from blockunit where blockid in (
  select bu.blockid from blockunit bu inner join interactionparts ip on
(bu.blockid = ip.part)
  where ip.blockid in

(110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087,


124088,124562,124563,133358,133359,133409,133410,135304,135305,136096)
  union
  select bu.blockid from blockunit bu
  where bu.blockid in

(110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087,


124088,124562,124563,133358,133359,133409,133410,135304,135305,136096)
)

Thanks in advance,

Yves












--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php






--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] query optimization - DB

2008-09-26 Thread Glen Synergy
MySQL  5.0 can only use 1 index per table.
MySQL = 5.0 can use more than one via an index merge.

http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html

On Sat, Sep 27, 2008 at 2:47 AM, Micah Gersten [EMAIL PROTECTED] wrote:

 MySQL queries use 1 index per table, so to speed the query, we need to
 know what indices you have for the 2 tables.

 Thank you,
 Micah Gersten
 onShore Networks
 Internal Developer
 http://www.onshore.com



 Yves Sucaet wrote:
  Oh, sorry I forgot to mention this. It's a MySQL database.
 
  - Original Message - From: Micah Gersten [EMAIL PROTECTED]
  To: YVES SUCAET [EMAIL PROTECTED]
  Cc: php-db@lists.php.net
  Sent: Thursday, September 25, 2008 7:55 PM
  Subject: Re: [PHP-DB] query optimization
 
 
  Other question is, what DB is this for?
 
  Thank you,
  Micah Gersten
  onShore Networks
  Internal Developer
  http://www.onshore.com
 
 
 
  YVES SUCAET wrote:
  How could I rewrite the following query so it runs faster:
 
  select distinct location from blockunit where blockid in (
select bu.blockid from blockunit bu inner join interactionparts ip on
  (bu.blockid = ip.part)
where ip.blockid in
 
 
 (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087,
 
 
  124088,124562,124563,133358,133359,133409,133410,135304,135305,136096)
union
select bu.blockid from blockunit bu
where bu.blockid in
 
 
 (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087,
 
 
  124088,124562,124563,133358,133359,133409,133410,135304,135305,136096)
  )
 
  Thanks in advance,
 
  Yves
 
 
 
 
 
 
 
 

 --
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php




Re: [PHP-DB] query optimization

2008-09-25 Thread Micah Gersten
What indices do you have?

Thank you,
Micah Gersten
onShore Networks
Internal Developer
http://www.onshore.com



YVES SUCAET wrote:
 How could I rewrite the following query so it runs faster:

 select distinct location from blockunit where blockid in (
   select bu.blockid from blockunit bu inner join interactionparts ip on
 (bu.blockid = ip.part) 
   where ip.blockid in 
  
 (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087,
   124088,124562,124563,133358,133359,133409,133410,135304,135305,136096)
   union 
   select bu.blockid from blockunit bu 
   where bu.blockid in 
  
 (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087,
   124088,124562,124563,133358,133359,133409,133410,135304,135305,136096)
 )

 Thanks in advance,

 Yves



   

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] query optimization

2008-09-25 Thread Chris

Micah Gersten wrote:

What indices do you have?

Thank you,
Micah Gersten
onShore Networks
Internal Developer
http://www.onshore.com



YVES SUCAET wrote:

How could I rewrite the following query so it runs faster:

select distinct location from blockunit where blockid in (
  select bu.blockid from blockunit bu inner join interactionparts ip on
(bu.blockid = ip.part) 
  where ip.blockid in 
 
(110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087,

  124088,124562,124563,133358,133359,133409,133410,135304,135305,136096)
  union 
  select bu.blockid from blockunit bu 
  where bu.blockid in 
 
(110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087,

  124088,124562,124563,133358,133359,133409,133410,135304,135305,136096)
)


Which parts are slow?

Run the inner query by itself to see if that's slow.

If it is, take the first part of the union and run that. Is that slow?

Same for the second.


Also since you're doing a DISTINCT in the outer query, you can change 
the subquery to do a UNION ALL.


A UNION will remove duplicates from the result sets, a UNION ALL will 
not. Since you're doing a distinct on the whole thing anyway, remove the 
duplicate check from the subquery - it'll make it slightly faster.


--
Postgresql  php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] query optimization

2008-09-25 Thread Jack van Zanen
Hi

If I am not mistaken,
the second part of the union contains all rows that are in the first part of
the union. just remove the first part.

Also


What is the table sizes of the tables?
How many records are expected to come back from the union sub query?
How many records are expected to come back from the main query
What is the current execution plan?

Jack




2008/9/26 YVES SUCAET [EMAIL PROTECTED]

 How could I rewrite the following query so it runs faster:

 select distinct location from blockunit where blockid in (
  select bu.blockid from blockunit bu inner join interactionparts ip on
 (bu.blockid = ip.part)
  where ip.blockid in


 (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087,
  124088,124562,124563,133358,133359,133409,133410,135304,135305,136096)
  union
  select bu.blockid from blockunit bu
  where bu.blockid in


 (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087,
  124088,124562,124563,133358,133359,133409,133410,135304,135305,136096)
 )

 Thanks in advance,

 Yves



 --
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php




-- 
J.A. van Zanen


Re: [PHP-DB] query optimization

2008-09-25 Thread Chris

Jack van Zanen wrote:

Hi

If I am not mistaken,
the second part of the union contains all rows that are in the first part of
the union. just remove the first part.


Kind of.

The first part is a join, the second isn't.

I was going to suggest rewriting the subquery into a single:

where
ip.blockid in (...)
or
bu.blockid in (...)

however that'll probably be slower, but def. worth a try.

--
Postgresql  php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] query optimization

2008-09-25 Thread Jack van Zanen
If you can answer the other questions that would help as well

you can try rewriting using exist instead of in

But without the basic information  like number of records expected and
explain plan it is very hard to come up with a better solution.


Brgds

Jack

2008/9/26 Chris [EMAIL PROTECTED]

 Jack van Zanen wrote:

 Hi

 If I am not mistaken,
 the second part of the union contains all rows that are in the first part
 of
 the union. just remove the first part.


 Kind of.

 The first part is a join, the second isn't.

 I was going to suggest rewriting the subquery into a single:

 where
 ip.blockid in (...)
 or
 bu.blockid in (...)

 however that'll probably be slower, but def. worth a try.


 --
 Postgresql  php tutorials
 http://www.designmagick.com/




-- 
J.A. van Zanen


Re: [PHP-DB] query optimization

2008-09-25 Thread Micah Gersten
Other question is, what DB is this for?

Thank you,
Micah Gersten
onShore Networks
Internal Developer
http://www.onshore.com



YVES SUCAET wrote:
 How could I rewrite the following query so it runs faster:

 select distinct location from blockunit where blockid in (
   select bu.blockid from blockunit bu inner join interactionparts ip on
 (bu.blockid = ip.part) 
   where ip.blockid in 
  
 (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087,
   124088,124562,124563,133358,133359,133409,133410,135304,135305,136096)
   union 
   select bu.blockid from blockunit bu 
   where bu.blockid in 
  
 (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087,
   124088,124562,124563,133358,133359,133409,133410,135304,135305,136096)
 )

 Thanks in advance,

 Yves



   

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query

2008-04-30 Thread Chris
Ron Piggott wrote:
 I have an interesting question.
 
 When I run the following query through my PHP script it produces 1
 result.  I know this because I echo the value of $num to the screen from
 the following syntax: $num=mysql_numrows($result);
 
 When I do the query in the SQL tab of phpMyAdmin there are 6 results.
 The 6 requests are correct.  
 
 Any ideas why I have 2 different search results?

Show us the php code.. maybe you're overwriting the $result variable in
your loop.


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query

2008-04-30 Thread Ron Piggott

You just drew my attention my include that brings in the site menu and
was changing the conflict.  Thanks :)  Ron

On Thu, 2008-05-01 at 13:19 +1000, Chris wrote:
 Ron Piggott wrote:
  I have an interesting question.
  
  When I run the following query through my PHP script it produces 1
  result.  I know this because I echo the value of $num to the screen from
  the following syntax: $num=mysql_numrows($result);
  
  When I do the query in the SQL tab of phpMyAdmin there are 6 results.
  The 6 requests are correct.  
  
  Any ideas why I have 2 different search results?
 
 Show us the php code.. maybe you're overwriting the $result variable in
 your loop.
 


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query Across DBMS Boundaries (Linked Database Servers?)

2008-03-24 Thread Chris

Dee Ayy wrote:

Is there something which can perform a query across different database systems?

It would extend the database.table.field notation to maybe
dbms.database.table.field and allow queries across MySQL and MSSQL


I don't think so.

Both mysql and mssql would need to know how to parse that syntax and 
make it all work (besides other problems like database permissions, 
different connection strings and so on).


Depending on how much data you are looking to move from one to the 
other, you could get mssql to export to a csv file (if it supports it - 
no idea) then get mysql to import it. Either use the csv storage engine 
(http://dev.mysql.com/doc/refman/4.1/en/csv-storage-engine.html) or load 
it into another table using 'load data infile' 
(http://dev.mysql.com/doc/refman/4.1/en/load-data.html).


If you need to do it on the fly, my only other suggestion would be to 
set up two database connections - one for mssql and one for mysql and 
aggregate the data in php.


--
Postgresql  php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query table / results to an array

2008-02-29 Thread Daniel Brown
On Fri, Feb 29, 2008 at 12:22 AM, Ron Piggott
[EMAIL PROTECTED] wrote:
  $reference_number = mysql_result($search_result,$i,reference_number);
  $description = mysql_result($search_result,$i,description);

?
$sql = SELECT reference_number FROM table WHERE this='whatever' OR
that NOT LIKE '%whatever%' LIMIT 0,1;
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
?

Just mind the line-wrapping in this message.  ;-P

-- 
/Dan

Daniel P. Brown
Senior Unix Geek
? while(1) { $me = $mind--; sleep(86400); } ?

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query table / results to an array

2008-02-28 Thread Chris

Ron Piggott wrote:

I need help populating an array based on the output of a mysql query.


$reference_number is a value assigned by auto_increment
$description is what I want the value of the array to be --- a few words
in length

$reference_number = mysql_result($search_result,$i,reference_number);
$description = mysql_result($search_result,$i,description);



$references = array();

$result = select id, description from table;
while ($row = mysql_fetch_assoc($result)) {
  $references[$row['id']] = htmlentities($row['description'], 
ENT_QUOTES, 'ISO-8859-1');

}

print_r($references);


--
Postgresql  php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query table / results to an array

2008-02-28 Thread Chris

Ron Piggott wrote:

This line of code

while ($row = mysql_fetch_assoc($result)) {

Gave me this error message:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL
result resource

I ran 


select reference, description from table

in mySQL and it worked --- Any idea what caused the error?


Oops..

$result = mysql_query(select id, description from table);



On Fri, 2008-02-29 at 16:39 +1100, Chris wrote:

Ron Piggott wrote:

I need help populating an array based on the output of a mysql query.


$reference_number is a value assigned by auto_increment
$description is what I want the value of the array to be --- a few words
in length

$reference_number = mysql_result($search_result,$i,reference_number);
$description = mysql_result($search_result,$i,description);


$references = array();

$result = select id, description from table;
while ($row = mysql_fetch_assoc($result)) {
   $references[$row['id']] = htmlentities($row['description'], 
ENT_QUOTES, 'ISO-8859-1');

}

print_r($references);








--
Postgresql  php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



RE: [PHP-DB] Query executing

2007-10-05 Thread Hutchins, Richard
The mysql_affected_rows() function will tell you how many rows were affected
by the DELETE query you just fired. You can also test for TRUE or FALSE on
the mysql_query() function if you just want to know if the query was
successful or not.

Rich


-Original Message-
From: ron.php [mailto:[EMAIL PROTECTED] 
Sent: Friday, October 05, 2007 9:52 AM
To: php-db@lists.php.net
Subject: [PHP-DB] Query executing

How would I know if this mySQL query:

DELETE FROM `table` WHERE `date_to_be_deleted` LIKE '$todays_date'

actually deleted any rows from the table?

Ron

 

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query syntax

2007-09-15 Thread TG

SELECT * FROM table
WHERE listing_type LIKE '%$listing_type%'
AND (listing_approved = '1' OR listing_approved = '2')
ORDER BY name ASC

(notice the parentheses around the OR part of the clause)

or you can do this...

SELECT * FROM table
WHERE listing_type LIKE '%$listing_type%'
AND listing_approved IN ('1', '2')
ORDER BY name ASC

Good luck!

-TG

- Original Message -
From: ron.php [EMAIL PROTECTED]
To: php-db@lists.php.net
Date: Sat, 15 Sep 2007 15:04:59 -0400
Subject: [PHP-DB] Query syntax

 $query=SELECT * FROM table WHERE listing_type LIKE '%$listing_type%' AND  
 listing_approved = '1' OR listing_approved = '2' ORDER BY name ASC;
 
 My question is how can I search for records where listing_approved has a 
 value of either 1 or 2 (while in the same search I am searching for 
 listing_type --- ie other variables)
 
 Ron
 
 -- 
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php
 
 

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query select value on MAX Date

2006-02-25 Thread Neil Smith [MVP, Digital media]

At 08:47 25/02/2006, you wrote:

MIME-Version: 1.0
Content-Type: multipart/alternative;
boundary=_=_NextPart_001_01C63997.1A6B1B7D
Date: Sat, 25 Feb 2006 08:07:36 +0900
Message-ID: [EMAIL PROTECTED]
From: [EMAIL PROTECTED]
To: 'php-db@lists.php.net'
Subject: FW: [PHP-DB] Query select value on MAX Date

Dear All,

 Sorry, I wrote the wrong result.


You've posted 3x with different results so it's too confusing to work 
out what you *want* to get.


However I woudl write that query like so :

SELECT MAX(Date) as NewestDate, Value
FROM table
GROUP BY Category
HAVING Date=NewestDate

HTH
Cheers - Neil

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



RE: [PHP-DB] Query select value on MAX Date

2006-02-24 Thread Nur_Adman
Dear All,

 

I have table like this :

 

Id |   Category |   Date|
Value

1  |   A  | 2005-02-21   |
2000

2  |   A  | 2004-01-21   |
3000

3  |   B  | 2006-01-20   |
4000

4  |   B  | 2005-12-11   |
5000

 

I want to get the value for each Category where the date is MAX.

So I make the query like this :

 

SELECT MAX(Date), Value FROM table GROUP BY Category

 

But I get is :

 

Id |   Category |   Date|
Value

1  |   A  | 2005-02-21   |
2000

3  |   B  | 2006-01-20   |
4000

 

Could you help me please?

 

Thanks  Regards,

 

Anita

 

 

 

 

 

 

 

 



Re: [PHP-DB] Query that generates href links

2005-03-20 Thread Evert | Rooftop Solutions
John Burroughs wrote:
Hi everyone,
I've created a database for my poetry that I've written. I have a 
table called Titles. It is made up of:
[snip]
How can I do this. Everytime I tried adding to the above code, I kept 
getting error messages in php saying that there was an unexpected 
''character on line such and such, but I don't know how I could even 
check to see which line had the error? (I'm using myphpadmin to manage 
my MySQL database.)

Now my connection to my MySQL server for my online hosting company has 
been done for 12 hours and I haven't been able to work on it anymore.

Does anyone know an easy way to do this?
Thanks,
John Burroughs
Hi John,
The line number is the line number of your php code where the error 
occurs. Looking at the code here, there shouldn't be anything wrong with 
it, the error is probably somewhere else in your code. I'd suggest you'd 
look for unterminated strings, like echo(hi there).

grt,
Evert
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


RE: [PHP-DB] Query that generates href links

2005-03-20 Thread Juffermans, Jos
Hi,

Please send the code that you used with the hrefs so we can see where the
error occurs. Tell us what line the error occured (and what line in your
copied code that is).

Jos

-Original Message-
From: John Burroughs [mailto:[EMAIL PROTECTED]
Sent: 21 March 2005 03:29
To: php-db@lists.php.net
Subject: [PHP-DB] Query that generates href links


Hi everyone,

I've created a database for my poetry that I've written. I have a table 
called Titles. It is made up of:

title_id (a different number for every poem title. It's the primary key.)
title (the title of the poem)
title_date (the date I wrote the poem. stored in Date format.)

I want to generate an html page that lists all of the poems that I've 
written in descending order by date. I want each title to be in an href 
link so my visitors can just click on a link, which will automatically 
generate a page to display that poem.

So for example, each finished line would look like . . .

pa href=poem.php?titleid=1Poem Title 1/a - 1/25/05/p

pa href=poem.php?titleid=2Poem Title 2/a - 1/20/05/p

pa href=poem.php?titleid=3Poem Title 3/a - 1/15/05/p

This is my code fragment. This works for printing the Titles and dates 
in descending order, but that was as far as I could get.

//Select the poetry database
 if ([EMAIL PROTECTED](bdweb320883_poetry)) {
  echo(pUnable to locate the poetry  . database at this 
time./p);
  exit();
   }
 //Result the titles of all of the poems that I've written

   $result = @mysql_query(Select title, title_date from titles order by 
title_date DESC);
 if(!$result) {
   echo(pError performing query:  . mysql_error() . /p);
   exit();
   }
 //Display the title of each poem
 while ($row = mysql_fetch_array($result)) {
   $poetrytitle = $row['title'];
   $poetrydate = $row['title_date'];
 echo(p . $poetrytitle .  -  . $poetrydate . 
/p);
   }

How can I do this. Everytime I tried adding to the above code, I kept 
getting error messages in php saying that there was an unexpected 
''character on line such and such, but I don't know how I could even 
check to see which line had the error? (I'm using myphpadmin to manage 
my MySQL database.)

Now my connection to my MySQL server for my online hosting company has 
been done for 12 hours and I haven't been able to work on it anymore.

Does anyone know an easy way to do this?

Thanks,

John Burroughs

-- 

John Burroughs
http://johnaburroughs.com



---
avast! Antivirus: Outbound message clean.
Virus Database (VPS): 0511-1, 03/17/2005
Tested on: 3/20/2005 9:28:43 PM
avast! - copyright (c) 1988-2004 ALWIL Software.
http://www.avast.com

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query was empty - Strange problem

2005-03-16 Thread Vinayakam Murugan
This is a common procedure. $Query is being passed to it. As I had
said before, i had tried echoing $Query before and after the
mysql_query statement and it displays a proper query.

That's what driving me nuts! :-(

On Wed, 16 Mar 2005 22:36:13 -0800, Stephen Johnson [EMAIL PROTECTED] wrote:
 I do not see where you are populating $Query -- therefore your query
 would be empty --
 
 
 On Mar 16, 2005, at 10:30 PM, Vinayakam Murugan wrote:
 
  Hi
 
  I have an application in which i am connecting to three different
  databases. I have three  connect and query procedure. I am facing
  problems with one of these databases.
 
  ---
  
  $Conn = mysql_pconnect($Hostname,$Loginname,$Passwd);
 
  $Ret = mysql_select_db ($Dbname, $Conn);
 
  if (!$Ret)
  {
  $Error = Error opening Database : $Dbname BR;
  return FALSE;
  }
 
  $Ret = mysql_query ($Query, $Conn) or $Error = BRBR .
  mysql_errno() . --- . mysql_error() . BRBR;
 
  echo $Error;
  ---
  
 
  I am getting an Error - Query was empty.
 
  I have tried echoin $Query before and after the mysql_query. It shows
  a proper query which if I copy and execute in phpmyadmin, i get the
  required results. It is not workign through PHP only.
 
 
  Any pointers would be very helpful.
 
  --
  Warm Regards
  
  Vinayak
 
  --
  PHP Database Mailing List (http://www.php.net/)
  To unsubscribe, visit: http://www.php.net/unsub.php
 
 
 *
 Stephen Johnson
 [EMAIL PROTECTED]
 http://www.thelonecoder.com
 
 --continuing the struggle against bad code--
 *
 
 


-- 
Warm Regards

Vinayak

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query was empty - Strange problem

2005-03-16 Thread Vinayakam Murugan
Here is the entire function

function Executequery($Query)
{
global $Error, $Hostname,$Loginname,$Passwd,$Dbname,$Conn;

$Conn = mysql_pconnect($Hostname,$Loginname,$Passwd);

$Ret = mysql_select_db ($Dbname, $Conn);

if (!$Ret)
{
$Error = Error opening Database : $Dbname BR;
return FALSE;
}

$Ret = mysql_query ($Query, $Conn) or $Error = BRBR .
mysql_errno() . --- . mysql_error() . BRBR;


if (!$Error)
{
$numfields = mysql_num_fields($Ret);
$numrows = mysql_num_rows($Ret);
if($numrows == 0)
{
$Error = Query returned zero records;
return FALSE;
}
}
else
{
return FALSE;
}


}


On Wed, 16 Mar 2005 22:36:13 -0800, Stephen Johnson [EMAIL PROTECTED] wrote:
 I do not see where you are populating $Query -- therefore your query
 would be empty --
 
 
 On Mar 16, 2005, at 10:30 PM, Vinayakam Murugan wrote:
 
  Hi
 
  I have an application in which i am connecting to three different
  databases. I have three  connect and query procedure. I am facing
  problems with one of these databases.
 
  ---
  
  $Conn = mysql_pconnect($Hostname,$Loginname,$Passwd);
 
  $Ret = mysql_select_db ($Dbname, $Conn);
 
  if (!$Ret)
  {
  $Error = Error opening Database : $Dbname BR;
  return FALSE;
  }
 
  $Ret = mysql_query ($Query, $Conn) or $Error = BRBR .
  mysql_errno() . --- . mysql_error() . BRBR;
 
  echo $Error;
  ---
  
 
  I am getting an Error - Query was empty.
 
  I have tried echoin $Query before and after the mysql_query. It shows
  a proper query which if I copy and execute in phpmyadmin, i get the
  required results. It is not workign through PHP only.
 
 
  Any pointers would be very helpful.
 
  --
  Warm Regards
  
  Vinayak
 
  --
  PHP Database Mailing List (http://www.php.net/)
  To unsubscribe, visit: http://www.php.net/unsub.php
 
 
 *
 Stephen Johnson
 [EMAIL PROTECTED]
 http://www.thelonecoder.com
 
 --continuing the struggle against bad code--
 *
 
 


-- 
Warm Regards

Vinayak

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query was empty - Strange problem

2005-03-16 Thread Stephen Johnson
I do not see where you are populating $Query -- therefore your query  
would be empty --

On Mar 16, 2005, at 10:30 PM, Vinayakam Murugan wrote:
Hi
I have an application in which i am connecting to three different
databases. I have three  connect and query procedure. I am facing
problems with one of these databases.
--- 

$Conn = mysql_pconnect($Hostname,$Loginname,$Passwd);

$Ret = mysql_select_db ($Dbname, $Conn);
if (!$Ret)
{
$Error = Error opening Database : $Dbname BR;
return FALSE;
}
$Ret = mysql_query ($Query, $Conn) or $Error = BRBR .
mysql_errno() . --- . mysql_error() . BRBR;
echo $Error;
--- 


I am getting an Error - Query was empty.
I have tried echoin $Query before and after the mysql_query. It shows
a proper query which if I copy and execute in phpmyadmin, i get the
required results. It is not workign through PHP only.
Any pointers would be very helpful.
--
Warm Regards

Vinayak
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

*
Stephen Johnson
[EMAIL PROTECTED]
http://www.thelonecoder.com
--continuing the struggle against bad code--
*
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


Re: [PHP-DB] query is not executing data is not entering in created database

2004-12-26 Thread amol patil
hallo,
 
see below code.   query is not executing and data is not shown in table of 
database.after submit button hit.
 
how to evaluate  $submit true. have  Tried these statements before if statement
 
 echo --$submit--;

echo --$_POST[submit]--;

echoSubmit = $submit ;

but still query is not executing.
 
 
 
?php
 
if($submit)
{

 $dbh=mysql_connect (localhost, root) or die ('I cannot connect to 
the database because: ' . mysql_error());
mysql_select_db (dollar1_allinfo);

 mysql_query(INSERT INTO totalinfo (Username,Password) VALUES 
('$loginusername','$loginpassword'))or die (mysql_error());




}
?
 
 
suggest changes in above code to execute query.
 
 
thank you.







-
Do you Yahoo!?
 Yahoo! Mail - Easier than ever with enhanced search. Learn more.

Re: [PHP-DB] query is not executing data is not entering in created database

2004-12-26 Thread John Holmes
amol patil wrote:
hallo,
 
see below code.   query is not executing and data is not shown in table of database.after submit button hit.
 
how to evaluate  $submit true. have  Tried these statements before if statement
 
 echo --$submit--;

echo --$_POST[submit]--;
echoSubmit = $submit ;
but still query is not executing.
The reason people told you to put the above code is for troubleshooting. 
It's not going to fix your query. What do you actually see resulting 
from the above code before and after you hit your submit button? My 
crystal ball says you see this:



Submit =
Am I right?
?php
 
if($submit)
{

 $dbh=mysql_connect (localhost, root) or die ('I cannot connect to 
the database because: ' . mysql_error());
mysql_select_db (dollar1_allinfo);

 mysql_query(INSERT INTO totalinfo (Username,Password) VALUES 
('$loginusername','$loginpassword'))or die (mysql_error());
So if you see what I guessed above, then $submit is not set, so whatever 
code you have here not going to execute. Do you know how an IF 
conditional works? Do you know that if it evaluates to FALSE, then 
nothing within the conditional block is executed? Your query is probably 
fine, but the code is just never getting to it.

I could tell you to use $_GET['submit'] or $_REQUEST['submit'], but 
would you know why or where to use it?

--
---John Holmes...
Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/
php|architect: The Magazine for PHP Professionals  www.phparch.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


RE: [PHP-DB] query of two tables returns too many rows, many more than the two tables contain

2004-11-11 Thread Gryffyn, Trevor
This is a common join issue.  If you don't specify ANYTHING to connect
between the two tables, it'll do one row from the first table, then ALL
the rows from the second.  Row #2 from the first, then ALL the rows from
the second.

If you had 600 rows in each table, you'd end up with 360,000 rows as a
returned result.

Chances are, you're just not being specific about what links the two
tables.

You might try something like this:

SELECT dealers.account_no,
   dealers.DealerName,
   blackgate_users.User_Name, 
   blackgate_users.DealerName
FROM dealers, blackgate_users
WHERE dealers.account_no  blackgate_users.User_Name
AND dealers.DealerName = blackgate_users.DealerName


Or something like that.


Just read the last thing you wrote.. Let me revise what I said then.

You say you want everything where there's an entry in Dealers, but no
corresponding entry in blackgate_users eh?  That's an outer join.  Try
this (syntax happy with SQL Server, I don't use MySQL a lot so it might
be slightly different):

SELECT dealers.account_no,
   dealers.DealerName,
   blackgate_users.User_Name, 
   blackgate_users.DealerName
FROM dealers left join blackgate_users on dealers.DealerName =
blackgate_users.DealerName
WHERE blackgate_users.DealerName is null


What this says is take everything in Dealers, left join it against
blackgate_users (left join says to take everything from the left side..
And match against the right side but leave NULL entries where there's no
match).   Then we tell it that the field we want to compare is
DealnerName in both cases.  The WHERE clause says only show us where
DealerName is null (meaning no corresponding record in blackgate_users).


I think that'll do it for ya.

-TG


 -Original Message-
 From: Chip Wiegand [mailto:[EMAIL PROTECTED] 
 Sent: Thursday, November 11, 2004 4:28 PM
 To: PHP DB
 Subject: [PHP-DB] query of two tables returns too many rows, 
 many more than the two tables contain
 
 
 I have two tables I want to get out the rows that are 
 different between 
 them. The results I am getting is almost 50,000 rows, but the 
 two tables, 
 combined, contain only about 600 rows total. Here is the 
 select statement 
 -
 
 SELECT dealers.account_no, dealers.DealerName, 
 blackgate_users.User_Name, 
 blackgate_users.DealerName
 FROM dealers, blackgate_users
 WHERE dealers.account_no NOT 
 LIKE blackgate_users.User_Name
 
 in these tables the 
 dealers.account_no is the same data as the blackgate_users.User_Name
 dealers.DealerName is the same data as the blackgate_users.DealerName
 I just want the rows that are in the dealers table but not in the 
 blackgate_users table. 
 
 Thanks for any help,
 Chip Wiegand
 Computer Services
 Simrad, Inc
 425-778-8821 
 425-771-7211 (FAX)
 
 -- 
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php
 
 

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



RE: [PHP-DB] query of two tables returns too many rows, many more than the two tables contain

2004-11-11 Thread Norland, Martin
-Original Message-
From: Chip Wiegand [mailto:[EMAIL PROTECTED] 
Sent: Thursday, November 11, 2004 3:28 PM
To: PHP DB
Subject: [PHP-DB] query of two tables returns too many rows, many more
than the two tables contain

I have two tables I want to get out the rows that are different between
them.
snip
in these tables the dealers.account_no is the same data as the
blackgate_users.User_Name dealers.DealerName is the same data as the
blackgate_users.DealerName I just want the rows that are in the dealers
table but not in the blackgate_users table. 

===

You want to do a join on the two tables, and make it conditional upon a
known failing value (e.g. if when they DO match up, blackgate_users
shouldn't be NULL - make that your condition).  That will return just
the rows in the first table that don't have a match.

cheers,
- Martin Norland, Database / Web Developer, International Outreach x3257
The opinion(s) contained within this email do not necessarily represent
those of St. Jude Children's Research Hospital.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



RE: [PHP-DB] query of two tables returns too many rows, many more than the two tables contain

2004-11-11 Thread Chip Wiegand
Gryffyn, Trevor [EMAIL PROTECTED] wrote on 11/11/2004 
01:39:37 PM:

 This is a common join issue.  If you don't specify ANYTHING to connect
 between the two tables, it'll do one row from the first table, then ALL
 the rows from the second.  Row #2 from the first, then ALL the rows from
 the second.
snip
 Just read the last thing you wrote.. Let me revise what I said then.
 
 You say you want everything where there's an entry in Dealers, but no
 corresponding entry in blackgate_users eh?  That's an outer join.  Try
 this (syntax happy with SQL Server, I don't use MySQL a lot so it might
 be slightly different):
 
 SELECT dealers.account_no,
dealers.DealerName,
blackgate_users.User_Name, 
blackgate_users.DealerName
 FROM dealers left join blackgate_users on dealers.DealerName =
 blackgate_users.DealerName
 WHERE blackgate_users.DealerName is null
 
Thanks for the help. That gets me much closer. I did a count in both 
tables and figured there should be 121 rows returned by the query. The 
above select statement gets me 141 rows returned. With a little sleuthing 
around in there I will probably figure out what the extra 10 rows are.
Thanks you very much.
Regards,
Chip
 
 What this says is take everything in Dealers, left join it against
 blackgate_users (left join says to take everything from the left side..
 And match against the right side but leave NULL entries where there's no
 match).   Then we tell it that the field we want to compare is
 DealnerName in both cases.  The WHERE clause says only show us where
 DealerName is null (meaning no corresponding record in blackgate_users).
 
 
 I think that'll do it for ya.
 
 -TG
 
 
  -Original Message-
  From: Chip Wiegand [mailto:[EMAIL PROTECTED] 
  Sent: Thursday, November 11, 2004 4:28 PM
  To: PHP DB
  Subject: [PHP-DB] query of two tables returns too many rows, 
  many more than the two tables contain
  
  
  I have two tables I want to get out the rows that are 
  different between 
  them. The results I am getting is almost 50,000 rows, but the 
  two tables, 
  combined, contain only about 600 rows total. Here is the 
  select statement 
  -
  
  SELECT dealers.account_no, dealers.DealerName, 
  blackgate_users.User_Name, 
  blackgate_users.DealerName
  FROM dealers, blackgate_users
  WHERE dealers.account_no NOT 
  LIKE blackgate_users.User_Name
  
  in these tables the 
  dealers.account_no is the same data as the blackgate_users.User_Name
  dealers.DealerName is the same data as the blackgate_users.DealerName
  I just want the rows that are in the dealers table but not in the 
  blackgate_users table. 
  
  Thanks for any help,
  Chip Wiegand
  Computer Services
  Simrad, Inc
  425-778-8821 
  425-771-7211 (FAX)
  
  -- 
  PHP Database Mailing List (http://www.php.net/)
  To unsubscribe, visit: http://www.php.net/unsub.php
  
  

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query returns duplicate rows

2004-07-21 Thread Justin Patrin
On Wed, 21 Jul 2004 16:29:37 -0400, Brock Jimmy D Contr 74 MDSS/SGSI
[EMAIL PROTECTED] wrote:
 My query is returning duplicates rows.
 
 table: elements
 elementId
 standardId
 elementtext
 category
 mcode
 linenum
 
 table: scores
 scoreId
 taskId
 scores
 
 Here's my query:
 SELECT distinct elementtext,cateogy,mcode,linenum,scores,scoreId
 FROM elements, scores
 WHERE scores.taskId='12'
 AND elements.standardId='APR.05'
 
 This is returning duplicate rows, even though I'm using the DISTINCT keyword.
 
 If I remove the field scoreId it is fine.
 
 Any suggestions?
 

Should you perhaps be doing some kind of ON clause to that join? It's
joining every record in elements with every element in scores. You
need to tell it what fields to join on.

Also, doing a distinct on that many fields at once could be very
expensive. Better tp refine your query or data model.

-- 
DB_DataObject_FormBuilder - The database at your fingertips
http://pear.php.net/package/DB_DataObject_FormBuilder

paperCrane --Justin Patrin--

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



RE: [PHP-DB] Query returns duplicate rows

2004-07-21 Thread Brock Jimmy D Contr 74 MDSS/SGSI
These two tables do not have a relation, so there is nothing to join.

Basically, what I'm trying to is this:
Users need to score elements. The elements are assoicatied with standards.
A standard may have 1 to 29 elements associated with it.

For users to score the elements they need to see the criteria for that particular 
element.

Thanks for the help though.

-Original Message-
From: Justin Patrin
To: Brock Jimmy D Contr 74 MDSS/SGSI
Cc: [EMAIL PROTECTED]
Sent: 7/21/2004 5:02 PM
Subject: Re: [PHP-DB] Query returns duplicate rows

On Wed, 21 Jul 2004 16:29:37 -0400, Brock Jimmy D Contr 74 MDSS/SGSI
[EMAIL PROTECTED] wrote:
 My query is returning duplicates rows.
 
 table: elements
 elementId
 standardId
 elementtext
 category
 mcode
 linenum
 
 table: scores
 scoreId
 taskId
 scores
 
 Here's my query:
 SELECT distinct elementtext,cateogy,mcode,linenum,scores,scoreId
 FROM elements, scores
 WHERE scores.taskId='12'
 AND elements.standardId='APR.05'
 
 This is returning duplicate rows, even though I'm using the DISTINCT
keyword.
 
 If I remove the field scoreId it is fine.
 
 Any suggestions?
 

Should you perhaps be doing some kind of ON clause to that join? It's
joining every record in elements with every element in scores. You
need to tell it what fields to join on.

Also, doing a distinct on that many fields at once could be very
expensive. Better tp refine your query or data model.

-- 
DB_DataObject_FormBuilder - The database at your fingertips
http://pear.php.net/package/DB_DataObject_FormBuilder

paperCrane --Justin Patrin--

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query returns duplicate rows

2004-07-21 Thread Justin Patrin
On Wed, 21 Jul 2004 17:09:35 -0400, Brock Jimmy D Contr 74 MDSS/SGSI
[EMAIL PROTECTED] wrote:
 These two tables do not have a relation, so there is nothing to join.
 
 Basically, what I'm trying to is this:
 Users need to score elements. The elements are assoicatied with standards.
 A standard may have 1 to 29 elements associated with it.
 
 For users to score the elements they need to see the criteria for that particular 
 element.
 
 Thanks for the help though.
 
 

You may want to try selecting all selected elements, then all selected
scores, then loop through them individually. Doing a join without an
ON clause is IMHO not a good choice.

 
 -Original Message-
 From: Justin Patrin
 To: Brock Jimmy D Contr 74 MDSS/SGSI
 Cc: [EMAIL PROTECTED]
 Sent: 7/21/2004 5:02 PM
 Subject: Re: [PHP-DB] Query returns duplicate rows
 
 On Wed, 21 Jul 2004 16:29:37 -0400, Brock Jimmy D Contr 74 MDSS/SGSI
 [EMAIL PROTECTED] wrote:
  My query is returning duplicates rows.
 
  table: elements
  elementId
  standardId
  elementtext
  category
  mcode
  linenum
 
  table: scores
  scoreId
  taskId
  scores
 
  Here's my query:
  SELECT distinct elementtext,cateogy,mcode,linenum,scores,scoreId
  FROM elements, scores
  WHERE scores.taskId='12'
  AND elements.standardId='APR.05'
 
  This is returning duplicate rows, even though I'm using the DISTINCT
 keyword.
 
  If I remove the field scoreId it is fine.
 
  Any suggestions?
 
 
 Should you perhaps be doing some kind of ON clause to that join? It's
 joining every record in elements with every element in scores. You
 need to tell it what fields to join on.
 
 Also, doing a distinct on that many fields at once could be very
 expensive. Better tp refine your query or data model.
 
 --
 DB_DataObject_FormBuilder - The database at your fingertips
 http://pear.php.net/package/DB_DataObject_FormBuilder
 
 paperCrane --Justin Patrin--
 


-- 
DB_DataObject_FormBuilder - The database at your fingertips
http://pear.php.net/package/DB_DataObject_FormBuilder

paperCrane --Justin Patrin--

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query for Most Recent Entry

2004-07-02 Thread John W. Holmes
Thompson, Jimi wrote:
I'm trying to figure out how to write a MySQL query that will return the
highest primary key a table.  
1) Why?
2) SELECT MAX(id) FROM table
3) If you're trying to find the key of the last row inserted to an 
auto_increment column, use mysql_insert_id() or LAST_INSERT_ID() in PHP 
and MySQL, respectively.

--
---John Holmes...
Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/
php|architect: The Magazine for PHP Professionals  www.phparch.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


Re: [PHP-DB] query repeating results???

2004-03-26 Thread Brent Baisley
Try left joining your states table also. MySQL may be joining the 
property_photos and the states table first, which would screw up your 
left join. Haven't had my morning coffee yet so I can't say for sure. 
Put EXPLAIN in front of your query to see what MySQL is doing.

SELECT
...
FROM
properties
LEFT JOIN states ON properties.state_id=states.id
LEFT JOIN property_photos ON property_photos.property_id=properties.id
WHERE
states.code='fl'
On Mar 26, 2004, at 1:45 AM, Katie Dewees wrote:

I am running the following query:

SELECT
...stuff...
FROM
properties LEFT JOIN property_photos ON
property_photos.property_id=properties.id,
states
WHERE
states.code='fl' AND
properties.state_id=states.id
LIMIT 0, 10
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search  Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


RE: [PHP-DB] Query to Array to Echo Problem--Thank you

2004-02-25 Thread Karen Resplendo
Yes!
That worked! (It was returning the same field values not column names, my mistake). 
Now it returns what it should return. Onwards to the next snafu!
Thanks!
 


Ryan Jameson (USA) [EMAIL PROTECTED] wrote:
One thing may be that you need to do a fetch to increment the result row
odbc_fetch_row($result);. Though that doesn't explain your column names
being in the array unless that's what's in the table.

 Ryan

-Original Message-
From: Karen Resplendo [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 24, 2004 5:18 PM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Query to Array to Echo Problem

Here is the webpage that demonstrates my code problem:
http://170.104.158.16/chemlatestPAGETEST.php3?pwsno=00100

I'm so close I can smell it. Hoping someone can troubleshoot my code.
Here is the piece that doesn't work. It returns the 2 column names, over
and over. For some reason I'm not loading the values from the query.
(Using only 2 columns for now and hardcoding number of rows):

function QueryIntoArray($query){
settype($retval,array);
/* connect to sdwis server*/
$connectionSDWIS = odbc_connect(,,);
if(!$connectionSDWIS) die(Couldn't connect);
/* select sdwis database */
odbc_do($connectionSDWIS, use );

$result= odbc_do($connectionSDWIS,$query); if(!$result){
print Query Failed; } for($i=0;$i100;$i++){
$fields=odbc_num_fields($result);
for($j=1;$j $fields;$j++){
$retval[$i][odbc_field_name($result,$j)] =
odbc_result($result,$j);
}//end inner loop
}//end outer loop
return $retval;
}//end function


$query = SELECT DISTINCT PWS, PWSName, CONVERT(varchar(12),
DateCollected, 101), AnalyteName, SourceID, str(Results,7,4), str(MCL,
7,4), UOM, DateCollected FROM ChemMon WHERE PWSID='00100' AND
AnalyteCode NOT IN('3013','3014','3100') ORDER BY DateCollected DESC,
AnalyteName ;

settype($myresult,array);
$myresult = QueryIntoArray($query);
for($i=0;$i print $myresult[$i][AnalyteName];
print $myresult[$i][UOM];
}



-
Do you Yahoo!?
Yahoo! Mail SpamGuard - Read only the mail you want.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


-
Do you Yahoo!?
Yahoo! Mail SpamGuard - Read only the mail you want.

[PHP-DB] Re: php-db Query

2004-02-24 Thread Frank Flynn
Peppe

Don't search by month at all - use a start and stop date then order by date.

Now you have one result set with all the dates in it and you can use your
PHP code to figure out where to close one table and open a net one:

 $result = mysql_query($sql);

 echo TABLE;
  $thisMonth = $result[3];  // I'm assuming this is a month year combo
// you'll have to do more work here.

 while (!$result -EOF)
{
if ($thisMonth != $result[3]) {
   
$thisMonth = $result[3];
echo '/TABLETABLE';
}
echo 'TR' . $result[0] . '/TDTD ' . $result[1] . '/TD/TR';

$result-MoveNext(); // Moves to the next row

} // end while
 echo /TABLE;


 
   $rowArray = mysql_fetch_array($result);
   $datum= $rowArray[datum];
   $title   =$rowArray[title];
   $datum= date(d-m-Y, strtotime($datum));
 $datum is 20-02-2004

On 2/24/04 8:20 AM, [EMAIL PROTECTED]
[EMAIL PROTECTED] wrote:

 
 From: peppe [EMAIL PROTECTED]
 Reply-To: peppe [EMAIL PROTECTED]
 Date: Tue, 24 Feb 2004 17:20:19 +0100
 To: [EMAIL PROTECTED]
 Subject: Query
 
 Hi I have this query
 $sql = SELECT * FROM events WHERE ( YEAR(datum) = ' . $year . ') ORDER BY
 datum ASC;
 $result = mysql_query($sql);
 $numRows  = mysql_num_rows($result);
 for ($row = 1; $row = $numRows; $row++) {
 
   $rowArray = mysql_fetch_array($result);
   $datum= $rowArray[datum];
   $title   =$rowArray[title];
   $datum= date(d-m-Y, strtotime($datum));
 $datum is 20-02-2004
 ?
 I want to show here the events by month for example table with events of
 02(February) then if there are events from 03(March) another table and so
 one
 How can I make this work do I need to make another query
 Any idea
 


-- 
Frank Flynn
Poet, Artist  Mystic

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



RE: [PHP-DB] Query to Array to Echo Problem

2004-02-24 Thread Ryan Jameson (USA)
One thing may be that you need to do a fetch to increment the result row
odbc_fetch_row($result);.  Though that doesn't explain your column names
being in the array unless that's what's in the table.

 Ryan

-Original Message-
From: Karen Resplendo [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 24, 2004 5:18 PM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Query to Array to Echo Problem

Here is the webpage that demonstrates my code problem:
http://170.104.158.16/chemlatestPAGETEST.php3?pwsno=00100
 
I'm so close I can smell it. Hoping someone can troubleshoot my code.
Here is the piece that doesn't work. It returns the 2 column names, over
and over. For some reason I'm not loading the values from the query.
(Using only 2 columns for now and hardcoding number of rows):
 
function QueryIntoArray($query){
 settype($retval,array);
 /* connect to sdwis server*/
$connectionSDWIS = odbc_connect(,,);
if(!$connectionSDWIS) die(Couldn't connect);
/* select sdwis database */
odbc_do($connectionSDWIS, use );
 
$result= odbc_do($connectionSDWIS,$query);  if(!$result){
   print Query Failed;  }  for($i=0;$i100;$i++){
$fields=odbc_num_fields($result);
  for($j=1;$j $fields;$j++){
$retval[$i][odbc_field_name($result,$j)] =
odbc_result($result,$j);
   }//end inner loop
  }//end outer loop
return $retval;
}//end function
 
 
$query = SELECT DISTINCT PWS, PWSName, CONVERT(varchar(12),
DateCollected, 101), AnalyteName, SourceID, str(Results,7,4), str(MCL,
7,4), UOM, DateCollected FROM ChemMon WHERE PWSID='00100'  AND
AnalyteCode NOT IN('3013','3014','3100') ORDER BY DateCollected DESC,
AnalyteName ;

settype($myresult,array);
$myresult = QueryIntoArray($query);
for($i=0;$icount($myresult);$i++){
   print $myresult[$i][AnalyteName];
   print $myresult[$i][UOM];
}
 


-
Do you Yahoo!?
Yahoo! Mail SpamGuard - Read only the mail you want.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query Sum problem

2003-12-16 Thread CPT John W. Holmes
From: Larry Sandwick [EMAIL PROTECTED]
 I need to sum the field *COST* in this query where data in *STATUS* is
 equal to HELD and OPEN, so I will have 2 totals passed below and do
 not know where to begin .

SELECT status, SUM(cost) FROM Table WHERE status IN ('HELD','OPEN') GROUP BY
status

---John Holmes...

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query Sum problem

2003-12-16 Thread CPT John W. Holmes
From: Larry Sandwick [EMAIL PROTECTED]

 I appreciate the quick response, but I should have been more clear.

 I understand the query below, but I would only have 2 total and it is
 not group by the companies.

 The query below gives me the main information without totals.

 How do I add the 2 totals to the query below?

 SELECT DISTINCT(Company), account, City, State FROM Table WHERE number =
 100

SELECT Company, Account, City, State, SUM(IF(Status='Held',Cost,0)) AS Held,
SUM(IF(Status='Open',Cost,0)) AS Cost FROM Table WHERE Number = 100

---John Holmes...

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query Case In-sensitive

2003-11-19 Thread mustafa ocak
Use LIKE clause instead of =

SELECT item_number FROM  item WHERE  item_code LIKE  'M1234' ;

   This will perform case-insensitive matching on all databases I used
before (Including Oracle, MySQL, SQL-Server)




- Original Message - 
From: ramki [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Tuesday, November 18, 2003 5:46 AM
Subject: Re: [PHP-DB] Query Case In-sensitive


 Generally in DBs, data is case sensitive.

 Simply you can do an uppercase or lowercase conversion in both sides.

 Ex : Select item number from item where upper(item_code) = upper(m1234);

 -ramki
 - Original Message - 
 From: Larry Sandwick [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Sent: Tuesday, November 18, 2003 1:23 AM
 Subject: [PHP-DB] Query Case In-sensitive


  Is there a way to run a query so that it ignores the case, and the query
  is not case sensitive?
 
  The data being stored is m1234
 
  Query
 
  Select item number from item where item_code = M1234 ;
 
  The query above should return the data above.
 
  TIA
 
 
 
  Larry Sandwick
 
  Sarreid, Ltd.
 
  Network/System Administrator
 
  phone: (252) 291-1414 x223
 
  fax  : (252) 237-1592
 
 
 
 

 -- 
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query Case In-sensitive

2003-11-19 Thread roy.a.jones
As an Oracle DBA (9+ years, versions 7.x - 9.x) I just wanted to make a 
correction.  The LIKE command uses wild card characters ( _ and % ) for 
substitutions.  Oracle IS a case-sensitive RDBMS and the LIKE command will 
NOT do case-insensitive queries.


Roy A. Jones 
US Pharma Database Administration 
GlaxoSmithKline Inc. US Pharma IT, Shared Services 
External: (919) 483-0266 
Internal: 703-0266 
Fax: (919) 315-6842 
Office: RC2 - 2005 
Email: [EMAIL PROTECTED] 
http://usphdba.gsk.com/ - USPHARMA Database Site 



mustafa ocak [EMAIL PROTECTED] 
19-Nov-2003 07:11
 
To
[EMAIL PROTECTED]
cc

Subject
Re: [PHP-DB] Query Case In-sensitive






Use LIKE clause instead of =

SELECT item_number FROM  item WHERE  item_code LIKE  'M1234' ;

   This will perform case-insensitive matching on all databases I used
before (Including Oracle, MySQL, SQL-Server)




- Original Message - 
From: ramki [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Tuesday, November 18, 2003 5:46 AM
Subject: Re: [PHP-DB] Query Case In-sensitive


 Generally in DBs, data is case sensitive.

 Simply you can do an uppercase or lowercase conversion in both sides.

 Ex : Select item number from item where upper(item_code) = 
upper(m1234);

 -ramki
 - Original Message - 
 From: Larry Sandwick [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Sent: Tuesday, November 18, 2003 1:23 AM
 Subject: [PHP-DB] Query Case In-sensitive


  Is there a way to run a query so that it ignores the case, and the 
query
  is not case sensitive?
 
  The data being stored is m1234
 
  Query
 
  Select item number from item where item_code = M1234 ;
 
  The query above should return the data above.
 
  TIA
 
 
 
  Larry Sandwick
 
  Sarreid, Ltd.
 
  Network/System Administrator
 
  phone: (252) 291-1414 x223
 
  fax  : (252) 237-1592
 
 
 
 

 -- 
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php





Re: [PHP-DB] Query Case In-sensitive

2003-11-19 Thread mustafa ocak
Yes you are right,

I have tried the queries on Oracle and MySQL, it works right on MySQL (case 
insensitive)  but not on Oracle.
Thank you for the correction.


 


  - Original Message - 
  From: [EMAIL PROTECTED] 
  To: [EMAIL PROTECTED] ; [EMAIL PROTECTED] 
  Sent: Wednesday, November 19, 2003 2:36 PM
  Subject: Re: [PHP-DB] Query Case In-sensitive



  As an Oracle DBA (9+ years, versions 7.x - 9.x) I just wanted to make a correction.  
The LIKE command uses wild card characters ( _ and % ) for substitutions.  Oracle IS a 
case-sensitive RDBMS and the LIKE command will NOT do case-insensitive queries.


  Roy A. Jones 

--
  US Pharma Database Administration 
  GlaxoSmithKline Inc. US Pharma IT, Shared Services 

--
  External: (919) 483-0266 
  Internal: 703-0266 
  Fax: (919) 315-6842 
  Office: RC2 - 2005 
  Email: [EMAIL PROTECTED] 

--
  http://usphdba.gsk.com/ - USPHARMA Database Site 


mustafa ocak [EMAIL PROTECTED] 
19-Nov-2003 07:11 
   To [EMAIL PROTECTED]  
  cc  
  Subject Re: [PHP-DB] Query Case In-sensitive 

  

   



  Use LIKE clause instead of =

  SELECT item_number FROM  item WHERE  item_code LIKE  'M1234' ;

This will perform case-insensitive matching on all databases I used
  before (Including Oracle, MySQL, SQL-Server)




  - Original Message - 
  From: ramki [EMAIL PROTECTED]
  To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
  Sent: Tuesday, November 18, 2003 5:46 AM
  Subject: Re: [PHP-DB] Query Case In-sensitive


   Generally in DBs, data is case sensitive.
  
   Simply you can do an uppercase or lowercase conversion in both sides.
  
   Ex : Select item number from item where upper(item_code) = upper(m1234);
  
   -ramki
   - Original Message - 
   From: Larry Sandwick [EMAIL PROTECTED]
   To: [EMAIL PROTECTED]
   Sent: Tuesday, November 18, 2003 1:23 AM
   Subject: [PHP-DB] Query Case In-sensitive
  
  
Is there a way to run a query so that it ignores the case, and the query
is not case sensitive?
   
The data being stored is m1234
   
Query
   
Select item number from item where item_code = M1234 ;
   
The query above should return the data above.
   
TIA
   
   
   
Larry Sandwick
   
Sarreid, Ltd.
   
Network/System Administrator
   
phone: (252) 291-1414 x223
   
fax  : (252) 237-1592
   
   
   
   
  
   -- 
   PHP Database Mailing List (http://www.php.net/)
   To unsubscribe, visit: http://www.php.net/unsub.php
  

  -- 
  PHP Database Mailing List (http://www.php.net/)
  To unsubscribe, visit: http://www.php.net/unsub.php





Re: [PHP-DB] Query Case In-sensitive

2003-11-18 Thread Andrei Migatchev
you can convert both of the sides to one case, similar to this:

select item_number from item where upper (item_code) = upper (M1234);

this way you ensure that both the value in the database and the selection 
variable are in one and the same case: capital. this should work in just 
about every single database, there's always a function to convert a string 
to upper case, the syntax might be different to the above example (which 
is mysql). just consult your dbms documentation.

ciao,
andrei
.



Larry Sandwick [EMAIL PROTECTED] 
2003/11/17 09:53 PM
Please respond to
[EMAIL PROTECTED]


To
[EMAIL PROTECTED]
cc

Subject
[PHP-DB] Query Case In-sensitive






Is there a way to run a query so that it ignores the case, and the query
is not case sensitive?





The data being stored is m1234



Query

Select item number from item where item_code = M1234 ;



The query above should return the data above.



TIA



Larry Sandwick

Sarreid, Ltd.

Network/System Administrator

phone: (252) 291-1414 x223

fax  : (252) 237-1592






Re: [PHP-DB] Query Case In-sensitive

2003-11-17 Thread roy.a.jones
In Oracle you have a couple of choices.

(1) If you can ensure that the data stored is in one case you can apply a 
function to the keyed in data
SELECT item_number FROM item WHERE item_code = lower('M1234');

(2) If you can not ensure data case then you can apply the function to 
both side
SELECT item_number FROM item WHERE lower(item_code) = 
lower('M1234');

You can use triggers on your tables to do case conversions then ensure 
case in the table.
In later versions of Oracle you can apply function-based indexes to your 
table to speed up these queries.


Roy A. Jones 
US Pharma Database Administration 
GlaxoSmithKline Inc. US Pharma IT, Shared Services 
External: (919) 483-0266 
Internal: 703-0266 
Fax: (919) 315-6842 
Office: RC2 - 2005 
Email: [EMAIL PROTECTED] 
http://usphdba.gsk.com/ - USPHARMA Database Site 



Larry Sandwick [EMAIL PROTECTED] 
17-Nov-2003 14:53
Please respond to [EMAIL PROTECTED]

 
To
[EMAIL PROTECTED]
cc

Subject
[PHP-DB] Query Case In-sensitive






Is there a way to run a query so that it ignores the case, and the query
is not case sensitive?

 

 

The data being stored is m1234

 

Query

Select item number from item where item_code = M1234 ;

 

The query above should return the data above.

 

TIA

 

Larry Sandwick

Sarreid, Ltd.

Network/System Administrator

phone: (252) 291-1414 x223

fax  : (252) 237-1592

 




Re: [PHP-DB] Query Case In-sensitive

2003-11-17 Thread CPT John W. Holmes
From: Larry Sandwick [EMAIL PROTECTED]

 Is there a way to run a query so that it ignores the case, and the query
 is not case sensitive?

What's this have to do with PHP and what database are you using?

---John Holmes...   

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query Case In-sensitive

2003-11-17 Thread Pedro Pais
Hey!

At least in Postgresql you can do something like:
SELECT * FROM table where item_code ILIKE 'blabla';
But I guess it's standard, so you can use it in any DB.

Larry Sandwick wrote:
Is there a way to run a query so that it ignores the case, and the query
is not case sensitive?
 

 

The data being stored is m1234

 

Query

Select item number from item where item_code = M1234 ;

 

The query above should return the data above.

 

TIA

 

Larry Sandwick

Sarreid, Ltd.

Network/System Administrator

phone: (252) 291-1414 x223

fax  : (252) 237-1592

 


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


Re: [PHP-DB] Query Case In-sensitive

2003-11-17 Thread ramki
Generally in DBs, data is case sensitive. 

Simply you can do an uppercase or lowercase conversion in both sides.

Ex : Select item number from item where upper(item_code) = upper(m1234);

-ramki
- Original Message - 
From: Larry Sandwick [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, November 18, 2003 1:23 AM
Subject: [PHP-DB] Query Case In-sensitive


 Is there a way to run a query so that it ignores the case, and the query
 is not case sensitive?
 
 The data being stored is m1234
 
 Query
 
 Select item number from item where item_code = M1234 ;
 
 The query above should return the data above.
 
 TIA
 
  
 
 Larry Sandwick
 
 Sarreid, Ltd.
 
 Network/System Administrator
 
 phone: (252) 291-1414 x223
 
 fax  : (252) 237-1592
 
  
 
 

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query or code?

2003-11-06 Thread CPT John W. Holmes
From: Peter Beckman [EMAIL PROTECTED]


 I have this data:

 Table Log: appid   userid points  datetype
 Table Score: appid userid score

 I want to verify that the last entry in table log of type x is equal to
 the sum of the scores in table score for the same appid and userid.

 Can I do this in SQL easily?  My problem is selecting the correct (most
 recent) row in log in which to match the score.

 Basically I want a report of AppID, TeamMemberID, log.points, score.score
 that shows where points != score;

Why do you have a score table at all? That's just repeating data when you
can always do a SUM query on the log table to get the score...

Athough...

SQL example:

mysql select * from log;
++--+
| id | log  |
++--+
|  1 |1 |
|  1 |2 |
|  1 |3 |
|  1 |4 |
|  2 |3 |
|  2 |5 |
++--+
6 rows in set (0.00 sec)

mysql select * from score;
++---+
| id | score |
++---+
|  1 |10 |
|  2 | 1 |
++---+
2 rows in set (0.00 sec)

mysql select s.id, s.score, sum(l.log) as log_sum from score s, log l where
l.id = s.id group by l.id;
++---+-+
| id | score | log_sum |
++---+-+
|  1 |10 |  10 |
|  2 | 1 |   8 |
++---+-+
2 rows in set (0.00 sec)

mysql select s.id, s.score, sum(l.log) as log_sum from score s, log l where
l.id = s.id group by l.id having s.score != log_sum;
++---+-+
| id | score | log_sum |
++---+-+
|  2 | 1 |   8 |
++---+-+
1 row in set (0.00 sec)

---John Holmes...

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query or code?

2003-11-06 Thread beckman
On Thu, 6 Nov 2003, CPT John W. Holmes wrote:

 From: Peter Beckman [EMAIL PROTECTED]

  I have this data:
 
  Table Log: appid   userid points  datetype
  Table Score: appid userid score
 
  I want to verify that the last entry in table log of type x is equal to
  the sum of the scores in table score for the same appid and userid.
 
  Can I do this in SQL easily?  My problem is selecting the correct (most
  recent) row in log in which to match the score.
 
  Basically I want a report of AppID, TeamMemberID, log.points, score.score
  that shows where points != score;

 Why do you have a score table at all? That's just repeating data when you
 can always do a SUM query on the log table to get the score...

 Athough...

 SQL example:

Almost; Here's the hard part:

mysql select logid, type, date, ApplicationID, TeamMemberID, Points from
log where applicationid=19933 and teammemberid=63 and type=Promotion
order by date desc;
+---+---+-+---+--++
| logid | type  | date| ApplicationID | TeamMemberID | Points |
+---+---+-+---+--++
|  2966 | Promotion | 2003-08-14 17:43:22 | 19933 |   63 | 71 |
|  2381 | Promotion | 2003-08-01 13:02:56 | 19933 |   63 | 81 |
|  2373 | Promotion | 2003-08-01 12:54:20 | 19933 |   63 | 81 |
|  2105 | Promotion | 2003-07-31 15:06:55 | 19933 |   63 | 84 |
+---+---+-+---+--++
4 rows in set (0.02 sec)

mysql select ApplicationID, TeamMemberID, sum(score) as score from score
where Applicationid=19933 and teammemberid=63 group by ApplicationID,
TeamMemberID;
+---+--+---+
| ApplicationID | TeamMemberID | score |
+---+--+---+
| 19933 |   63 |96 |
+---+--+---+
1 row in set (0.01 sec)

I want to see the comparison done with the most recent row of type
Promotion from the log table:
+---+--+---+---+
| ApplicationID | TeamMemberID | log_score | score |
+---+--+---+---+
| 19933 |   63 |71 |96 |
+---+--+---+---+

Can it be done with a single query?  I can do it brilliantly easily in
code, but I like the challenge of doing it in SQL.

---
Peter Beckman  Director of Internet Initiatives
North American Managementhttp://www.nambco.com/
703.683.0292 x239[EMAIL PROTECTED]
---

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query or code?

2003-11-06 Thread John W. Holmes
[EMAIL PROTECTED] wrote:
On Thu, 6 Nov 2003, CPT John W. Holmes wrote:
From: Peter Beckman [EMAIL PROTECTED]
I have this data:

   Table Log: appid   userid points  datetype
   Table Score: appid userid score
I want to verify that the last entry in table log of type x is equal to
the sum of the scores in table score for the same appid and userid.
Can I do this in SQL easily?  My problem is selecting the correct (most
recent) row in log in which to match the score.
Basically I want a report of AppID, TeamMemberID, log.points, score.score
that shows where points != score;
Why do you have a score table at all? That's just repeating data when you
can always do a SUM query on the log table to get the score...
I'd still like an answer to this question. Why is there a need for a 
separate table with scores?

Can it be done with a single query?  I can do it brilliantly easily in
code, but I like the challenge of doing it in SQL.
Without knowing the exact table structure, maybe this'll work?

mysql select s.applicationid, s.teammemberid, l.points, sum(s.score), 
date from score s, log l wher
e s.applicationid = l.applicationid group by l.points order by l.date 
desc limit 1;
+---+--++--+-+
| applicationid | teammemberid | points | sum(s.score) | date 
 |
+---+--++--+-+
| 19933 |   63 | 71 |   96 | 2003-08-14 
17:43:22 |
+---+--++--+-+
1 row in set (0.00 sec)

--
---John Holmes...
Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/

php|architect: The Magazine for PHP Professionals  www.phparch.com

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


Re: [PHP-DB] Query or code?

2003-11-06 Thread Peter Beckman
On Thu, 6 Nov 2003, John W. Holmes wrote:

 I'd still like an answer to this question. Why is there a need for a
 separate table with scores?

 The log is a snapshot in time -- what was the total points at the time
 of the log entry.

 The score table is always the accurate current score.

  Can it be done with a single query?  I can do it brilliantly easily in
  code, but I like the challenge of doing it in SQL.

 Without knowing the exact table structure, maybe this'll work?

 mysql select s.applicationid, s.teammemberid, l.points, sum(s.score),
 date from score s, log l where s.applicationid = l.applicationid group by
 l.points order by l.date desc limit 1;
 +---+--++--+-+
 | applicationid | teammemberid | points | sum(s.score) | date|
 +---+--++--+-+
 | 19933 |   63 | 71 |   96 | 2003-08-14 17:43:22 |
 +---+--++--+-+
 1 row in set (0.00 sec)

 That works for 1 row, but I want 30+ unique appid's and teammemberid's
 with the most recent date points.  Since some 2nd-to-last promotions
 occurred much later than the last promotion on others, ordering by date
 doesn't help either.

 Basically I need a report that says:

When comparing the last Promotion log entry on table log, these are the
applicationid and teammemberid combinations in which the sum of the
items matching in the score table does not equal the points in the
selected log entry.

Peter
---
Peter Beckman  Internet Guy
[EMAIL PROTECTED] http://www.purplecow.com/
---

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query Error

2003-11-04 Thread CPT John W. Holmes
From: Robert Sossomon [EMAIL PROTECTED]

 And here is the additem.php file where it check for the info in the
 field and it is erroring out.

It would be rather useful to know the error...

   $get_iteminfo = select * from GCN_items where 'item_num' LIKE
 '%$_POST[sel_item_id]%';

Take away the single quotes around your column name. You're asking for a
'string' LIKE 'another string' and not comparing anything in your column at
all.

You can use backticks around table and column names, though. Maybe that's
what you're trying to achieve.

$get_iteminfo = select * from GCN_items where `item_num` LIKE
'%$_POST[sel_item_id]%';

---John Holmes...

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



RE: [PHP-DB] Query Error

2003-11-04 Thread Robert Sossomon
The errors as it prints are:

The query I just ran was: select * from GCN_items where `item_num` =
'%fm-a294%'
The query I just ran was: Resource id #2
0



-Original Message-
From: CPT John W. Holmes [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, November 04, 2003 11:04 AM
To: Robert Sossomon; [EMAIL PROTECTED]
Subject: Re: [PHP-DB] Query Error


From: Robert Sossomon [EMAIL PROTECTED]

 And here is the additem.php file where it check for the info in the 
 field and it is erroring out.

It would be rather useful to know the error...

   $get_iteminfo = select * from GCN_items where 'item_num' LIKE 
 '%$_POST[sel_item_id]%';

Take away the single quotes around your column name. You're asking for a
'string' LIKE 'another string' and not comparing anything in your column
at all.

You can use backticks around table and column names, though. Maybe
that's what you're trying to achieve.

$get_iteminfo = select * from GCN_items where `item_num` LIKE
'%$_POST[sel_item_id]%';

---John Holmes...

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query Error

2003-11-04 Thread CPT John W. Holmes
From: Robert Sossomon [EMAIL PROTECTED]


 The errors as it prints are:

 The query I just ran was: select * from GCN_items where `item_num` =
 '%fm-a294%'
 The query I just ran was: Resource id #2
 0

Those aren't errors; it's just what you asked the script to display. Your
query simply isn't returning any rows.

You should be using LIKE instead of the equal sign, since I assume you're
looking for a pattern.

select * from GCN_items where `item_num` LIKE '%fm-a294%'

otherwise you're looking for the literal string %fm-a294%

---John Holmes...

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



RE: [PHP-DB] Query Error

2003-11-04 Thread Robert Sossomon
That fixed the output to actually find the information, however I'll be
danged if it is not adding the items to the database.

In fact I am seeing errors with it adding half the stuff to the database
from stuff that was working.

I rebooted the system.  Removed the database, stopped the server.
Started the server. Reloaded the database.  Refreshed the data.  Now I
am not seeing stuff added to the system and I have no idea why.

According to the scripts everything is working fine.  The information is
being passed to the pages correctly and even the output on the page
showed that the data was correct for insertion, however when I look at
the database where the information should be, it is not there.  I'm
losing my mind!

Any thoughts?

~~~
A horse! A horse! My kingdom for a horse!


-Original Message-
From: CPT John W. Holmes [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, November 04, 2003 1:31 PM
To: Robert Sossomon; [EMAIL PROTECTED]
Subject: Re: [PHP-DB] Query Error


From: Robert Sossomon [EMAIL PROTECTED]


 The errors as it prints are:

 The query I just ran was: select * from GCN_items where `item_num` = 
 '%fm-a294%' The query I just ran was: Resource id #2 0

Those aren't errors; it's just what you asked the script to display.
Your query simply isn't returning any rows.

You should be using LIKE instead of the equal sign, since I assume
you're looking for a pattern.

select * from GCN_items where `item_num` LIKE '%fm-a294%'

otherwise you're looking for the literal string %fm-a294%

---John Holmes...

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query works but can't echo variable :'(

2003-10-26 Thread Micah Stevens
You're using an alias of 'date' instead of DateOfOrder, so that should be 
reflected in the array key. 

So:

$Date = $OrderDate[date];


-Micah

On Sun October 26 2003 4:22 pm, Graeme McLaren wrote:
 Hey everyone, the following query and code works fine but I can't
 understand why I can't echo the $Date variable.  I guess it doesn't work
 fine then :(

 Any ideas ?

 G :)

 $TheDate = mysql_query(select DATE_FORMAT(DateOfOrder, '%d, %m, %Y') as
 date from Orders Where Orders.ID = '$TransID');

 while($OrderDate = mysql_fetch_array($TheDate))

 {

 $Date = $OrderDate[DateOfOrder];

 }

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] query for multiple values

2003-10-15 Thread Ignatius Reilly
SELECT * 
FROM table 
WHERE  column IN ( 'this', 'that', 'theother' )

Ignatius
_
- Original Message - 
From: Robbie Staufer [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, October 16, 2003 12:10 AM
Subject: [PHP-DB] query for multiple values


 Hi,
 
 Is there a way to construct a mysql query that will select * for 
 multiple values in the same column?
 
 For example:  SELECT * FROM table WHERE  column = 'this' and 'that' and 
 'theother'
 
 Thanks,
 Robbie
 
 -- 
 -*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
 Robbie Staufer
 NCAR/SCD
 1850 Table Mesa Dr. Rm. 42
 Boulder, CO. 80305
 (303) 497-1836
 
 -- 
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php
 
 

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



RE: [PHP-DB] Query Filter in GUI

2003-09-22 Thread Griffiths, Daniel
how are you testing the check boxes?, looks like the the query string is empty because 
both the tests you are doing in the code below return false.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 22 September 2003 15:01
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Query Filter in GUI


Good Morning PHP-DB Listers,

I have a PHP GUI in which a select box gets populated by a query of a 
MySQL DB for all CD's by all artists when the page is first opened. I am 
now trying to implement 
the same query but with a WHERE clause that filters the returned CD's to 
be displayed in the same select box according to the artist selected via a 
group of checkboxes (one per artist).  The problem so far is that if I try 
to implement some flow control (if checbox one is selected, append where 
artist='hendrix' to the select all CDs query, mysql_query() etc...), I get 
an error message saying that the query string is empty. So I am looking 
for some advice about how to implement the logic of having the same select 
box display the results of different but related queries.

For example, 

$query_1 = SELECT CD_title FROM CDs order by CD_title;
$result_1 = mysql_query($query_1) or die(Can't select CD_title from CDs 
. mysql_error());
while($row=mysql_fetch_array($result_1, MYSQL_BOTH)) ..etc...

works just fine.

But if I try to use some flow control to alter which query statement is 
assigned to $query_1...

if(checkbox one is selected)
{
$query_1 = SELECT CD_title FROM CDs WHERE artist_name = 'Hendrix' 
order by CD_title;
}

elseif(no checkboxes are checked)
{ 
$query_1 = SELECT CD_title FROM CDs order by CD_title;
}

$result_1 = mysql_query($query_1) or die(Can't select CD_title from CDs 
. mysql_error());
while($row=mysql_fetch_array($result_1, MYSQL_BOTH)) ..etc...

produces the error message stated above ... that the query string is 
empty. I've also tried to duplicate the mysql_query($query_1) or 
die()... within each section of the IF group, but then the select box 
does not get populated at all.

I am using session variables, and I'm certain that the checkboxes are 
correctly assigning the necessary values to the variables associated with 
them because I am echo'ing the variables and I can see the values that 
they have.

And when I run the query with the where clause in the MySQL client, it 
works just fine.

I'm guessing I'm missing something fairly simple because this type of 
functionality must have been implemented before.

Thanks for reading the rather long question. 

David

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query Filter in GUI

2003-09-22 Thread CPT John W. Holmes
From: [EMAIL PROTECTED]

 I have a PHP GUI in which a select box gets populated by a query of a
 MySQL DB for all CD's by all artists when the page is first opened. I am
 now trying to implement
 the same query but with a WHERE clause that filters the returned CD's to
 be displayed in the same select box according to the artist selected via a
 group of checkboxes (one per artist).  The problem so far is that if I try
 to implement some flow control (if checbox one is selected, append where
 artist='hendrix' to the select all CDs query, mysql_query() etc...), I get
 an error message saying that the query string is empty. So I am looking
 for some advice about how to implement the logic of having the same select
 box display the results of different but related queries.

 For example,

 $query_1 = SELECT CD_title FROM CDs order by CD_title;
 $result_1 = mysql_query($query_1) or die(Can't select CD_title from CDs
 . mysql_error());
 while($row=mysql_fetch_array($result_1, MYSQL_BOTH)) ..etc...

 works just fine.

 But if I try to use some flow control to alter which query statement is
 assigned to $query_1...

 if(checkbox one is selected)
 {
 $query_1 = SELECT CD_title FROM CDs WHERE artist_name = 'Hendrix'
 order by CD_title;
 }

 elseif(no checkboxes are checked)
 {
 $query_1 = SELECT CD_title FROM CDs order by CD_title;
 }

 $result_1 = mysql_query($query_1) or die(Can't select CD_title from CDs
 . mysql_error());
 while($row=mysql_fetch_array($result_1, MYSQL_BOTH)) ..etc...

 produces the error message stated above ... that the query string is
 empty. I've also tried to duplicate the mysql_query($query_1) or
 die()... within each section of the IF group, but then the select box
 does not get populated at all.

In order for you to get that error, neither of your two conditions are TRUE.
Since you have pseudo-code, it's hard to tell where the problem is,
though.Something in (checkbox one is selected) and (no checkboxes are
checked) is not right.

Here's an easy way to do this, though, so pay attention. :)

Create your checkboxes like this:

input type=checkbox name=artist[] value=Hendrix
input type=checkbox name=artist[] value=Violent Femmes
etc...

Notice how they are all named the same and have different value.

Now, when processing this form, you'll have a variable $_GET['artist']
that's an array. If a checkbox was checked, then the array will have some
elements, otherwise it'll be empty and not set... so use something like
this:

if(isset($_GET['artist'])  is_array($_GET['artist']) 
!empty($_GET['artist']))
{
  $artist_list = ' . implode(',',$_GET['artist']) . ';
  $query = SELECT CD_title FROM CDs WHERE artist_name IN ($artist_list)
order by CD_title;
}
else
{
  $query = SELECT CD_title FROM CDs order by CD_title;
}

Then run your query and retrieve the results like you are doing now. What
this will to is take multiple checkboxes selected by the user (or just one
if you want) and make it into a comma separated list to send to the query.
So if two checkboxes are selected, you end up with a query such as:

WHERE artist_name IN ('Hendrix','Violent Femmes')

Hope that helps.

---John Holmes...

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query runs fine on Console but not in PHP

2003-08-20 Thread Robert Twitty
First FreeTDS should have built support for TDS protocol version 8.0.
The tds protocol version should also be set to 8.0 in interface file.
Details about this is included with the FreeTDS source distribution.

-- bob

On Mon, 18 Aug 2003, vish.kohli wrote:

 Thanks for your response.
 My script is running on Linux. I am trying to retrieve simple data such as
 text and numbers.
 I am not sure what the TDS protocol setting should be. What are the typical
 settings and how can I check it?

 Would appreciate any tips for debugging.

 Thanks.

 - Original Message -
 From: Robert Twitty [EMAIL PROTECTED]
 To: vish.kohli [EMAIL PROTECTED]
 Cc: [EMAIL PROTECTED]
 Sent: Monday, August 18, 2003 8:28 AM
 Subject: Re: [PHP-DB] Query runs fine on Console but not in PHP


  What platform are you using?  Since it appears that the script is
  crashing, you are probably on Linux or some other UNIX flavor.  If this is
  the case, the TDS protocol setting may not be correct.  What type of date
  are you retrieving with this stored procedure?
 
  -- bob
 
  On Sun, 17 Aug 2003, vish.kohli wrote:
 
   Hi,
  
   I am trying to execute a stored procedure in PHP via mssql_query ()
   function. I pass 8 parameters to it.
   The query runs fine in SQL Query Analyzer but when I run it thru PHP, it
   behaves strangely. I can get number of rows (mssql_num_rows()) and
 number of
   fields (mssql_num_fields()) in PHP, but when I try to execute
   mssql_fetch_object() or mssql_fetch_array() on the same result
 identifier, I
   get a Page could not be displayed (standard internet error page).
  
   Please help.
   Thanks in advance.
  
  
  
   --
   PHP Database Mailing List (http://www.php.net/)
   To unsubscribe, visit: http://www.php.net/unsub.php
  
  
 
 
  --
  PHP Database Mailing List (http://www.php.net/)
  To unsubscribe, visit: http://www.php.net/unsub.php
 


 --
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php




-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query runs fine on Console but not in PHP

2003-08-19 Thread vish.kohli
Thanks for your response.
My script is running on Linux. I am trying to retrieve simple data such as
text and numbers.
I am not sure what the TDS protocol setting should be. What are the typical
settings and how can I check it?

Would appreciate any tips for debugging.

Thanks.

- Original Message -
From: Robert Twitty [EMAIL PROTECTED]
To: vish.kohli [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Monday, August 18, 2003 8:28 AM
Subject: Re: [PHP-DB] Query runs fine on Console but not in PHP


 What platform are you using?  Since it appears that the script is
 crashing, you are probably on Linux or some other UNIX flavor.  If this is
 the case, the TDS protocol setting may not be correct.  What type of date
 are you retrieving with this stored procedure?

 -- bob

 On Sun, 17 Aug 2003, vish.kohli wrote:

  Hi,
 
  I am trying to execute a stored procedure in PHP via mssql_query ()
  function. I pass 8 parameters to it.
  The query runs fine in SQL Query Analyzer but when I run it thru PHP, it
  behaves strangely. I can get number of rows (mssql_num_rows()) and
number of
  fields (mssql_num_fields()) in PHP, but when I try to execute
  mssql_fetch_object() or mssql_fetch_array() on the same result
identifier, I
  get a Page could not be displayed (standard internet error page).
 
  Please help.
  Thanks in advance.
 
 
 
  --
  PHP Database Mailing List (http://www.php.net/)
  To unsubscribe, visit: http://www.php.net/unsub.php
 
 


 --
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query runs fine on Console but not in PHP

2003-08-18 Thread Robert Twitty
What platform are you using?  Since it appears that the script is
crashing, you are probably on Linux or some other UNIX flavor.  If this is
the case, the TDS protocol setting may not be correct.  What type of date
are you retrieving with this stored procedure?

-- bob

On Sun, 17 Aug 2003, vish.kohli wrote:

 Hi,

 I am trying to execute a stored procedure in PHP via mssql_query ()
 function. I pass 8 parameters to it.
 The query runs fine in SQL Query Analyzer but when I run it thru PHP, it
 behaves strangely. I can get number of rows (mssql_num_rows()) and number of
 fields (mssql_num_fields()) in PHP, but when I try to execute
 mssql_fetch_object() or mssql_fetch_array() on the same result identifier, I
 get a Page could not be displayed (standard internet error page).

 Please help.
 Thanks in advance.



 --
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php




-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query Cache not Working? MySQL 4.0.14-standard

2003-08-14 Thread CPT John W. Holmes
From: Matt Babineau [EMAIL PROTECTED]
 For some reason my MySQL doesn't seem to be caching queries!

And you expect PHP to solve this how? Do you want PHP to cache the queries?
You must say please...

You need to set the four variables mentioned at the following URL in your
my.cnf file.

http://www.mysql.com/doc/en/Query_Cache_Configuration.html

especially the last two that give the size limit for the cache and turn it
ON.

---John Holmes...


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] query works fine on console but not with PHP - why?

2003-08-03 Thread Matt

- Original Message - 
From: Aaron Wolski [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Sunday, August 03, 2003 9:59 AM
Subject: [PHP-DB] query works fine on console but not with PHP - why?
 The error I am getting on the browser is:
  
 Warning: mysql_fetch_array(): supplied argument is not a valid MySQL
 result resource in http://www.martekbiz.com/KCS/utils.inc on line 52
  
 Anyone have an idea as to what it up?

Try:
echo mysql_error();

 and see what mysql says.


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



RE: [PHP-DB] query works fine on console but not with PHP - why? SOLVED

2003-08-03 Thread Aaron Wolski
Hi and thanks,

Actually.. there was a typo in an if() statement just prior to it that
checked to see if a variable was set before proceeding with the query.

What a dumb-ass I am. You know you've been working way to long when you
can't do simple things.

Thanks for your comments!

Aaron

-Original Message-
From: Matt [mailto:[EMAIL PROTECTED] 
Sent: August 3, 2003 10:11 AM
To: Aaron Wolski; [EMAIL PROTECTED]
Subject: Re: [PHP-DB] query works fine on console but not with PHP -
why?


- Original Message - 
From: Aaron Wolski [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Sunday, August 03, 2003 9:59 AM
Subject: [PHP-DB] query works fine on console but not with PHP - why?
 The error I am getting on the browser is:
  
 Warning: mysql_fetch_array(): supplied argument is not a valid MySQL
 result resource in http://www.martekbiz.com/KCS/utils.inc on line 52
  
 Anyone have an idea as to what it up?

Try:
echo mysql_error();

 and see what mysql says.




-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] query works fine on console but not with PHP - why?

2003-08-03 Thread Doug Thompson
You have an error in your PHP code, just as the error says.
Including only the query, which you've already tested, isn't very helpful.

The error message is telling you that the result link identifier used in your fetch 
statement isn't the same as returned by the query statement OR there was an error 
returned by the query.

It's all in the manual.

Doug

On Sun, 3 Aug 2003 09:59:42 -0400, Aaron Wolski wrote:

Hi guys,
 
I have the following query which returns FINE through the console but
not in PHP
 
 
select t.id, t.colour, t.colourID, t.price, t.type, g.thread_index,
g.groupNameUrl FROM kcs_threads t LEFT JOIN kcs_threadgroups g ON t.id =
g.thread_index WHERE g.groupNameUrl = '0-500'
 
 
The error I am getting on the browser is:
 
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL
result resource in http://www.martekbiz.com/KCS/utils.inc on line 52
 
 
 
Anyone have an idea as to what it up?
 
Thanks!
 
Aaron




-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] query, display and grouping.. one way works but notanother

2003-07-26 Thread John W. Holmes
Aaron Wolski wrote:

Hi Guys,
 
Have a multiple select box. I'm trying to group columns and display
records under their paired groupings.
[snip]
What I'd like is to display them like:
 
Manufactuer  - Type
 
Manufacturer - Type
 
Manufacturer - Type
 
I tried this code but only seems to group one TYPE and move onto the
next manufacturer and group that type.:
 
echo select name=\patternThreads[]\ size=\10\ multiple
style=\font-family:monospace;\\n;
 
 
$query = select t.manufacturer, t.id, t.colour, t.colourID, t.type,
p.thread_index FROM kcs_threads t LEFT JOIN kcs_patternthreads p ON t.id
= p.thread_index WHERE p.pattern_index = '$id' OR p.pattern_index IS
NULL ORDER BY t.manufacturer, t.type;
 
$thread_manufacturer = '';
$thread_types = '';
 
$result = db_query($query);
while($thread = db_fetch($result)) {
 
 
if($thread_manufacturer !=
$thread['manufacturer']) { 
 
echo option value=\\
class=\adminEditLink\{$thread['manufacturer']};
$thread_manufacturer =
$thread['manufacturer'];

}
 
if($thread_types != $thread['type']) { 
 
echo  -
{$thread['type']};
$thread_types =
$thread['type'];

}
 
echo /option\n;
}
 
echo /select\n;
 
 
Any idea's where I am going wrong?
Wouldn't it just be:

while($thread = db_fetch($result)) {
{
  echo option value=\\ 
class=\adminEditLink\{$thread['manufacturer']} - 
{$thread['type']}/option\n;
}

Although I don't see where you're setting a value for the option in 
your code.

--
---John Holmes...
Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/

PHP|Architect: A magazine for PHP Professionals  www.phparch.com





--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


Re: [PHP-DB] query and display acting weird...

2003-07-25 Thread CPT John W. Holmes
Probably need:

ORDER BY t.manufacturer, t.colourID

---John Holmes...

- Original Message - 
From: Aaron Wolski [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, July 25, 2003 2:58 PM
Subject: [PHP-DB] query and display acting weird...


 Hi Guys,
  
 Code:
  
 echo select name=\patternThreads[]\ size=\10\ multiple
 style=\font-family:monospace;\\n;
 
 $query = select t.manufacturer, t.id, t.colour, t.colourID, t.type,
 p.thread_index FROM kcs_threads t LEFT JOIN kcs_patternthreads p ON t.id
 = p.thread_index WHERE p.pattern_index = '$id' OR p.pattern_index IS
 NULL ORDER BY t.colourID;
 
 $thread_manufacturer = '';
 
 $result = db_query($query);
 while($thread = db_fetch($result)) {
 
 if($thread_manufacturer != $thread['manufacturer']) { 
 echo option value=\\
 class=\adminEditLink\{$thread['manufacturer']}/option\n; 
 $thread_manufacturer = $thread['manufacturer'];
 }
 }
 
 echo /select\n;
  
 In my select box I am getting results like:
  
 Canterbury Cross Fabrics
 Zweigart
 Permin
 Zweigart
  
 I SHOULD be getting:
  
 Canterbury Cross Fabrics
 Zweigart
 Permin
  
  
 This was once working.. as it should have but today more data was dumped
 into the table which is causing the second Zweigart to be displayed. 
 
 If I group the manufacturer in the query then I only one record
 displayed for the corresponding manufacturers. 
 
 Any clue how I can make it so that everything is sorted under grouped
 manufacturers but still get ALL the results for the manufacturer at the
 same time? 
 
 Have over 4000 records which was causing a LONG loading time in the
 browser so John Holmes gave me the above query to replace where I had 3
 queries doing the same thing. 
 
 
 ANY thoughts would be greatly welcomed! 
 
 Thanks 
 
 Aaron
 

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query problem

2003-07-22 Thread Dirk Kredler
Am Dienstag, 22. Juli 2003 11:29 schrieb Ron Allen:
 This is what I have
 select * from ticket where Type = 'Line' and Closeddate = ' empty space'
 and have tried the following
 select * from ticket where Type = 'Line' and Closeddate = 'Null'
 select * from ticket where Type = 'Line' and Closeddate = 'NULL'

use

isNULL(Closedate) instead of Closedate = 'NULL'



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



RE: [PHP-DB] Query problem

2003-07-22 Thread jeffrey_n_Dyke

you can't use COL_NAME = NULL or COL_NAME = 'NULL' and get the desired
results , you have to check for IS NULL/ IS NOT NULLSo, change the
query to

SELECT * FROM TABLE WHERE MY_FIELD IS NULL  or,
SELECT * FROM TABLE WHERE MY_FIELD IS NOT NULL

From my understanding when you check for  COL_NAME = 'Null' you're actually
checking to see if your field contains a _string_  'Null'

hth
jeff




   
 
  Boaz Yahav 
 
  [EMAIL PROTECTED]To:   Ron Allen [EMAIL 
PROTECTED], [EMAIL PROTECTED]   
  .net.il cc: 
 
   Subject:  RE: [PHP-DB] Query problem
 
  07/22/2003 03:16 
 
  PM   
 
   
 
   
 




Assuming you run MySQL, why don't you just remove the  ' ?

SELECT * FROM MyTable WHERE MyField=NULL

Sincerely

berber

Visit http://www.weberdev.com/ Today!!!
To see where PHP might take you tomorrow.
Share your code : http://addexample.weberdev.com


-Original Message-
From: Ron Allen [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 22, 2003 11:29 AM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Query problem


I have done this in the past, but for some reason it isn't working now
(maybe a moron).  I am trying to select all of the empty or NULL fields
in a column.

This is what I have
select * from ticket where Type = 'Line' and Closeddate = ' empty space'
and have tried the following select * from ticket where Type = 'Line'
and Closeddate = 'Null' select * from ticket where Type = 'Line' and
Closeddate = 'NULL'

any clues 



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php






-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] query selection

2003-06-16 Thread CPT John W. Holmes
Okay... and? Do you have a question? Are you wanting us to write this for
free? Do you want bids on the project?

---John Holmes...

- Original Message - 
From: Ryan Holowaychuk [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, June 16, 2003 2:30 PM
Subject: [PHP-DB] query selection


I have a databae built, at the moment it has two tables.

The first table has 2 fields, an ID and description.  This is for a listing
of all the different samples that we have at the company.

When the user clicks on the description they want, it will open another page
that will show all the items that fit that description.

So in this case the first page shows:  Brochures, business cards, tickets,
etc

They then click on tickets I want it to show all the tickets that we have
done and then I will have a link from there they can click on and show all
the jobs in there.  but to even make it more complicated I only want it to
show the first job for that client based on the job number. (eg. Tickets
each client will have a back, front and inside to show) so when they click
on tickets they will see the teams we have done, and then from there they
can select a team. And then it will open all the examples they want in
there, and then if they click on the thumb it would go to a page I have set
up with the image at a large size.

Thanks
Ryan


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



RE: [PHP-DB] query selection

2003-06-16 Thread Ryan Holowaychuk
Sorry forgot to state the question.

No bids.  Looking for help.

the first list that is displayed has an ID for each product.  But when I
click on it, it goes to the next page but does not display the rest of the
products that are listed in the second table.

$query = SELECT  * FROM product_list  WHERE productid = id;

Id being the description ID that came off the first page.

-Original Message-
From: CPT John W. Holmes [mailto:[EMAIL PROTECTED] 
Sent: Monday, June 16, 2003 11:43 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]

Okay... and? Do you have a question? Are you wanting us to write this for
free? Do you want bids on the project?

---John Holmes...

- Original Message - 
From: Ryan Holowaychuk [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, June 16, 2003 2:30 PM
Subject: [PHP-DB] query selection


I have a databae built, at the moment it has two tables.

The first table has 2 fields, an ID and description.  This is for a listing
of all the different samples that we have at the company.

When the user clicks on the description they want, it will open another page
that will show all the items that fit that description.

So in this case the first page shows:  Brochures, business cards, tickets,
etc

They then click on tickets I want it to show all the tickets that we have
done and then I will have a link from there they can click on and show all
the jobs in there.  but to even make it more complicated I only want it to
show the first job for that client based on the job number. (eg. Tickets
each client will have a back, front and inside to show) so when they click
on tickets they will see the teams we have done, and then from there they
can select a team. And then it will open all the examples they want in
there, and then if they click on the thumb it would go to a page I have set
up with the image at a large size.

Thanks
Ryan


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] query selection

2003-06-16 Thread CPT John W. Holmes
 the first list that is displayed has an ID for each product.  But when I
 click on it, it goes to the next page but does not display the rest of the
 products that are listed in the second table.

 $query = SELECT  * FROM product_list  WHERE productid = id;

 Id being the description ID that came off the first page.

Well, 'id' should at least be a variable, right? I assume your links look
something like this:

http://www.yourdomain.com/page.php?id=X

where X is the 'id' number you want to pass to the above select statement.
If so, then you'd use:

$query = SELECT * FROM product_list WHERE productid = {$_GET['id']};
or
$query = SELECT * FROM product_list WHERE productid =  . $_GET['id'];

where $_GET['id'] is a variable containing the value passed in the URL. Make
sure you properly validate $_GET['id'] to make sure it's a valid ID (or at
least an integer) before you stick it in your SQL statement, also.

$id = (int)$_GET['id'];
$query = SELECT * FROM product_list WHERE productid = $id;

---John Holmes...


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Query takes to much time

2003-06-04 Thread Robert Twitty
What database are you using?

Also, are all the fields involved in the joins and whare clause
properly indexed?  Depending on the database you are using, it is possible
to determine if the indexed fields are being used.

-- bob

On Tue, 3 Jun 2003, André Sannerholt wrote:

 Hi everybody,

 now I have the following qery that still needs to much time:

 select distinct Anschriften.ID as aid, Anschriften.PLZ as aplz,
 Anschriften.STRASSE as astrasse, Anschriften.HNR as ahnr from Anschriften
 inner join Master on Anschriften.ID=Master.A
 inner join Master as m on Master.FN=m.FN
 inner join Personen on m.NN=Personen.ID
 where Personen.ID = $id

 It gives me the correct results
 I hope you can help me to get it faster!

 André



 --
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php




--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] query string

2003-06-03 Thread Ian Fingold
Awsome!

Thanks, That is exactly what I was looking for. Work great now!


Leif K-Brooks [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
 To answer your question, you should urlencode() the team name.  However,
 I would store the user in a cookie or session, and fetch the team name
 on each page load.  Otherwise, they can easily change which team they're
 using.

 Ian Fingold wrote:

 Sorry I don't think I explained very well... heres my situation...
 
 I have a mysql database set up... I have a user table with 2
 fields...team_name and fant_week.
 
 What I want to do is, depending on who is logged in, I want my link to
 change the value of 'team' to the logged in members team name, like wise
for
 the week.
 
 For example..
 if gorno is logged in and his team is called fun team the link will
 reflect his team and look like this...
 fant_stnd3.php?week=1team=fun team
 
 or if say... billbo is logged in and his team is called silly team
the
 link will look like this
 fant_stnd3.php?week=1team=silly team
 
 but again, my problem is that it's cutting off the team value when there
is
 a space in the string..
 
 
 
 G|rhan Vzen [EMAIL PROTECTED] wrote in message
 news:[EMAIL PROTECTED]
 
 
 On Sun, 2003-06-01 at 22:26, Ian Fingold wrote:
 
 
 I'm trying to build a query string for one of my links.. for example...
 fant_stnd3.php?week=1team=fun%20team
 
 I need to be able to grab the values of 'week' and 'team' from an
array,
 
 
 I
 
 
 can do that no problem, but when the code runs it cuts off the value if
 there is a space in the string. so instead of putting fun team in the
 query string it cuts it off and just puts.. fun 
 
 So my question, is there a function or any way to prevent this from
 happening?
 
 thanks.
 
 
Hi Ian,
  I have no idea what you are trying to do.. Well if you are getting
 those values from a URL, $week and $team variables in your php script
 should have the values in them.. In later versions of PHP, they will be
 _POST['week'] and _POST['team'] or _GET['week'] or _GET['team']
 depending on which method is used.. You don't need to parse the URL and
 run them through an array.. :)
   Gurhan
 
 
 
 
 
 
 
 

 --
 The above message is encrypted with double rot13 encoding.  Any
unauthorized attempt to decrypt it will be prosecuted to the full extent of
the law.





-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] query string

2003-06-02 Thread Gürhan Özen
On Sun, 2003-06-01 at 22:26, Ian Fingold wrote:
 I'm trying to build a query string for one of my links.. for example...
 fant_stnd3.php?week=1team=fun%20team
 
 I need to be able to grab the values of 'week' and 'team' from an array, I
 can do that no problem, but when the code runs it cuts off the value if
 there is a space in the string. so instead of putting fun team in the
 query string it cuts it off and just puts.. fun 
 
 So my question, is there a function or any way to prevent this from
 happening?
 
 thanks.

   Hi Ian, 
 I have no idea what you are trying to do.. Well if you are getting
those values from a URL, $week and $team variables in your php script
should have the values in them.. In later versions of PHP, they will be
_POST['week'] and _POST['team'] or _GET['week'] or _GET['team']
depending on which method is used.. You don't need to parse the URL and
run them through an array.. :)
  Gurhan


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] query string

2003-06-02 Thread Ian Fingold
Sorry I don't think I explained very well... heres my situation...

I have a mysql database set up... I have a user table with 2
fields...team_name and fant_week.

What I want to do is, depending on who is logged in, I want my link to
change the value of 'team' to the logged in members team name, like wise for
the week.

For example..
if gorno is logged in and his team is called fun team the link will
reflect his team and look like this...
fant_stnd3.php?week=1team=fun team

or if say... billbo is logged in and his team is called silly team the
link will look like this
fant_stnd3.php?week=1team=silly team

but again, my problem is that it's cutting off the team value when there is
a space in the string..



Gürhan Özen [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
 On Sun, 2003-06-01 at 22:26, Ian Fingold wrote:
  I'm trying to build a query string for one of my links.. for example...
  fant_stnd3.php?week=1team=fun%20team
 
  I need to be able to grab the values of 'week' and 'team' from an array,
I
  can do that no problem, but when the code runs it cuts off the value if
  there is a space in the string. so instead of putting fun team in the
  query string it cuts it off and just puts.. fun 
 
  So my question, is there a function or any way to prevent this from
  happening?
 
  thanks.

Hi Ian,
  I have no idea what you are trying to do.. Well if you are getting
 those values from a URL, $week and $team variables in your php script
 should have the values in them.. In later versions of PHP, they will be
 _POST['week'] and _POST['team'] or _GET['week'] or _GET['team']
 depending on which method is used.. You don't need to parse the URL and
 run them through an array.. :)
   Gurhan




-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] query string

2003-06-02 Thread Becoming Digital
Check out http://www.php.net/manual/en/ref.url.php

Edward Dudlik
Becoming Digital
www.becomingdigital.com


- Original Message -
From: Ian Fingold [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Sunday, 01 June, 2003 23:20
Subject: Re: [PHP-DB] query string


Sorry I don't think I explained very well... heres my situation...

I have a mysql database set up... I have a user table with 2
fields...team_name and fant_week.

What I want to do is, depending on who is logged in, I want my link to
change the value of 'team' to the logged in members team name, like wise for
the week.

For example..
if gorno is logged in and his team is called fun team the link will
reflect his team and look like this...
fant_stnd3.php?week=1team=fun team

or if say... billbo is logged in and his team is called silly team the
link will look like this
fant_stnd3.php?week=1team=silly team

but again, my problem is that it's cutting off the team value when there is
a space in the string..



Grhan zen [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
 On Sun, 2003-06-01 at 22:26, Ian Fingold wrote:
  I'm trying to build a query string for one of my links.. for example...
  fant_stnd3.php?week=1team=fun%20team
 
  I need to be able to grab the values of 'week' and 'team' from an array,
I
  can do that no problem, but when the code runs it cuts off the value if
  there is a space in the string. so instead of putting fun team in the
  query string it cuts it off and just puts.. fun 
 
  So my question, is there a function or any way to prevent this from
  happening?
 
  thanks.

Hi Ian,
  I have no idea what you are trying to do.. Well if you are getting
 those values from a URL, $week and $team variables in your php script
 should have the values in them.. In later versions of PHP, they will be
 _POST['week'] and _POST['team'] or _GET['week'] or _GET['team']
 depending on which method is used.. You don't need to parse the URL and
 run them through an array.. :)
   Gurhan




--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php





-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] query string

2003-06-02 Thread Gürhan Özen
On Sun, 2003-06-01 at 23:20, Ian Fingold wrote:
 Sorry I don't think I explained very well... heres my situation...
 
 I have a mysql database set up... I have a user table with 2
 fields...team_name and fant_week.
 
 What I want to do is, depending on who is logged in, I want my link to
 change the value of 'team' to the logged in members team name, like wise for
 the week.
 
 For example..
 if gorno is logged in and his team is called fun team the link will
 reflect his team and look like this...
 fant_stnd3.php?week=1team=fun team
 
 or if say... billbo is logged in and his team is called silly team the
 link will look like this
 fant_stnd3.php?week=1team=silly team
 
 but again, my problem is that it's cutting off the team value when there is
 a space in the string..
 

 Ok.. I can't say anything, unless i know where and how it is being cut
off.. From that scenario you said.. You should probably query the week
name and team name from the database and then redirect the browser to
the location.. 
so something like..
  $query= select weekname, teamname from tablename where
username=.($username). limit 1;
  $result=mysql_query($query);
  $row=mysql_fetch_array($result)
  
  header( Location:
http://servername/fant_stnd3.php?;.($row[weekname]).team=.($row[teamname]).);

Of course, you will need to replace servername, tablename and column
names with appropriate values..
I hope this helps..

Gurhan

 



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



  1   2   >