Re: [PHP-DB] Query does not work

[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

[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

[PHP-DB] Query will not work - SOLVED

[Ethan Rosenberg, PhD]

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

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

[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

[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

[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

[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

[PHP-DB] Query Question

[admin]

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-DB] Query syntax error?

[Harvey]

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

Re: [PHP-DB] Query syntax error?

[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?

[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

[PHP-DB] query help

[Vinay Kannan]

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.

Re: [PHP-DB] query help

[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

[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

[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

[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

[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

[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

[PHP-DB] Query for duplicate records

[Ron Piggott]

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. 

Re: [PHP-DB] Query for duplicate records

[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

[PHP-DB] Query stopping after 2 records?

[Miller, Terion]

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

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

[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?

[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?

[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

[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

Fwd: [PHP-DB] query optimization

[Bastien Koert]

On Sun, Sep 28, 2008 at 6:28 PM, Chris [EMAIL PROTECTED] wrote:

 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


If possible, see if you can have the sort space on the db table made bigger.
You could gain a lot of performance if you can adjust some the of settings
in the mysql control file.


sorry, hit reply instead of reply all (my bad)

-- 

Bastien

Cat, the other other white meat



-- 

Bastien

Cat, the other other white meat

Re: [PHP-DB] query optimization

[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

[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

[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

[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

[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

[PHP-DB] query optimization

[YVES SUCAET]

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

[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

[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

[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

[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

[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

[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

[PHP-DB] Query

[Ron Piggott]

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?

Ron

SELECT * FROM ministry_directory INNER JOIN
ministry_directory_listing_categories ON ministry_directory.entry =
ministry_directory_listing_categories.ministry_directory_entry WHERE
ministry_directory.listing_type = 2 AND
ministry_directory_listing_categories.ministry_directory_category_reference IN 
( 10 ) ORDER BY ministry_directory.name ASC


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

Re: [PHP-DB] Query

[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

[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

[PHP-DB] Query Criteria

[Nasreen Laghari]

Hi All,
I need help in below coding as it is not working.
What I'm trying to do here, if $type contains today value then bring all 
record which has today's date, If $type contains tomorrow bring all 
tomorrow's record. 
Do you think below coding is correct? becuase when I run this query I get 
exception but if I place only one $query and outside of if.. else  the same 
query runs without errors.
 
 
Thank you for your help
 
Regards
 
$query;

$date = date(d/m/y);

  if($type==today)
{
  $query = SELECT * FROM gig where gig_Date= $date;
}
else if($type==tomorrow)
{
  $tomorrow  = mktime(0, 0, 0, date(m)  , date(d)+1, date(Y));
  $query = SELECT * FROM gig where gig_Date= $tomorrow;

}
else if($type==week)
{
  $week  = mktime(0, 0, 0, date(m)  , date(d)+6, date(Y));
  $query = SELECT * FROM gig WHERE g.gig_Date = .$date. OR g.gig_Date 
=.$week.;
  
}

$result = mysql_query($query)or die(mysql_error());


  return $result;
  }



  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

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

[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

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

[Dee Ayy]

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
such as:
//$sql =
SELECT MySQLidentifier.aDatabase.aTable.aField,
MSSQLidentifier.anotherDatabase.anotherTable.anotherField
FROM MySQLidentifier.aDatabase.aTable,
MSSQLidentifier.anotherDatabase.anotherTable
WHERE MySQLidentifier.aDatabase.aTable.aKeyField =
MSSQLidentifier.anotherDatabase.anotherTable.anotherKeyField
//Or through JOINS, etc.

I've seen the linked server option in MSSQL.  Technically I can write
to MSSQL, but company policy prohibits it.  To steer clear of
ambiguity, personally I've decided that I will also not create
triggers (or any object) on MSSQL (which is a write in my mind).
Basically I will only read information from MSSQL.

I was thinking of PDO, which raises an interesting distinction of
data-access abstraction layer versus database abstraction (which as I
understand means no SQL rewrite or missing feature emulation -- SQL
rewrite example: TOP n prefix versus LIMIT n suffix).  The PDO
developers may be close to creating such a feature.
$MySQLidentifier = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
$MSSQLidentifier = new PDO('mssql:host=localhost;dbname=test', $user, $pass);
$dbh = new PDO_LinkedServers($MySQLidentifier, $MSSQLidentifier);
$dbh-exec($sql);

Looking at MSSQL's OPENQUERY syntax, I don't even know if MSSQL
provides this extended query notation.  It just seems to allow a query
on a remote (or distributed) server.

Regards.

-- 
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

[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

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

[Ron Piggott]

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);

Ron

-- 
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

[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

[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

[PHP-DB] Query executing

[ron.php]

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

 

RE: [PHP-DB] Query executing

[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

[PHP-DB] Query syntax

[ron.php]

$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

Re: [PHP-DB] Query syntax

[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

[PHP-DB] Query Object in PHP?

[js]

Hi list,


Is there any implementation of Query Object [1] written in PHP?
I spent some time searching on the net but no clues're found.

Thank you in advance.

[1] http://www.martinfowler.com/eaaCatalog/queryObject.html

--
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

[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

[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

 

 

 

 

 

 

 

 

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

[Nur_Adman]

Dear All,

 

Sorry, I wrote the wrong result.

 

The result I wanna get is :

 

 

Id |   Category |   Date|
Value

1  |   A  | 2005-02-21   |
2000

3  |   B  | 2006-01-20   |
4000

 

But I got :

 

Id |   Category |   Date|
Value

1  |   A  | 2005-02-21   |
3000

3  |   B  | 2006-01-20   |
5000

 

 

The MAX(Date) id correct, but the incorrect value...

 

 

Thanks  Regards,

Anita

 

 

 



From: Adman, Nur anita 
Sent: Saturday, February 25, 2006 6:05 AM
To: 'php-db@lists.php.net'
Subject: RE: [PHP-DB] Query select value on MAX Date

 

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

 

 

 

 

 

 

 

 

[PHP-DB] query on database determines image swap in php-coded page?

[Elaine Miller]

Hi, I'm a designer of simple webpages, and an enthusiastic user of 
PHP applications that run with MySQL -- but I don't write, or think 
in, PHP, so I can't quite figure how to do this:

I run phpbb forum software (yes, I've asked at phpbb and at
phpbbhacks, no luck) and I have installed for our members a chatroom
kind of thing.

In the header (an include on every page in the site), are .GIF images
for navigation. I have installed a little hack (someone else's 
cleverness) that makes the private message button in the header swap 
to an animated gif when someone has a private message. And I'd like 
to do the same for the chatroom thing, which runs off the same 
database as the messaging system and the main forum.

It would go kinda like: any page loads or refreshes, and header code
checks the database for the number of chatters in the chatroom. Zero
chatters = static button image, one or more chatters = animated gif
button image.

Can anyone help with this, or point me at a simple resource? 

I'd be happy to swap some work-time back, for the favour.

Thanks for taking the time to read...
-Elaine

Email? My domain is elainemiller, with an extension of .com, and the
thingy in front of the at sign would be Elaine. 

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

[PHP-DB] query error

[pete M]

I've got a database table with a whole list of windows file paths.
eg
Advent Tower PC\C\Program Files\Microsoft Office\Microsoft Binder.lnk
Advent Tower PC\C\Program Files\Microsoft Office\Microsoft Excel.lnk
Advent Tower PC\C\Program Files\Microsoft Office\Microsoft Office Setup.lnk
Advent Tower PC\C\Program Files\Microsoft Office\Microsoft Outlook.lnk
Advent Tower PC\C\Program Files\Microsoft Office\Microsoft PowerPoint.lnk
Advent Tower PC\C\Program Files\Microsoft Office\Microsoft Word.lnk
Advent Tower PC\C\Program Files\Microsoft Office\MS Access Workgroup 
Administrator.lnk
Advent Tower PC\C\Program Files\Microsoft Office\MSCREATE.DIR
Advent Tower PC\C\Program Files\Microsoft Office\OF97SPEC.INI
Advent Tower PC\C\Program Files\Microsoft Office\Office
Advent Tower PC\C\Program Files\Microsoft Office\Office\1033

Now I want my DB query to search for directories .. here's a sample query
select * from ff_files
where full_path
like 'Advent Tower PC\C\freeserve\help\images\%'
doesnt work as the % is taken as a literal '%'
I've aslso tried
like 'Advent Tower PC\\C\\freeserve\\help\\images\%'
I'm completely lost ..
all I want is to return all rows that start with
'Advent Tower PC\C\freeserve\help\images\'
tia
Pete
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Query that generates href links

[John Burroughs]

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

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

[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

[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

[PHP-DB] Query was empty - Strange problem

[Vinayakam Murugan]

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

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

[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

[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

[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

[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

[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

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

[Chip Wiegand]

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

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

[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

[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

[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

[PHP-DB] query parser

[Aditya Ivaturi]

We have a content management system and one of its features is what we call
a databse browser. What it allows you to do is take a table and display
contents in the way it is useful for the relevant audience. One of the new
features that was requested was editing the content which will in turn be
moderated. All pretty straight forward when you are dealing with individual
tables. Now it starts getting a little complex when we involve queries.

This database browser can also accept queries as input and display the
output. It pretty much takes the (pre-detremined) query sends it to mysql
and dumps the result out. Now how do we let people edit these results. Now
keep in mind any type of query that mysql supports can be used. One way
would be to find out columns are obtained as a result of this query and
store them in a temporary table and when they are updated change them in
their respective tables. Seems too easy. Any other suggestions or ideas?

--Turi

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

[PHP-DB] query parser

[Aditya Ivaturi]

We have a content management system and one of its features is what we call 
a databse browser. What it allows you to do is take a table and display 
contents in the way it is useful for the relevant audience. One of the new 
features that was requested was editing the content which will in turn be 
moderated. All pretty straight forward when you are dealing with individual 
tables. Now it starts getting a little complex when we involve queries.

This database browser can also accept queries as input and display the 
output. It pretty much takes the (pre-detremined) query sends it to mysql 
and dumps the result out. Now how do we let people edit these results. Now 
keep in mind any type of query that mysql supports can be used. One way 
would be to find out columns are obtained as a result of this query and 
store them in a temporary table and when they are updated change them in 
their respective tables. Seems too easy. Any other suggestions or ideas?

--Turi 

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

[PHP-DB] Query returns duplicate rows

[Brock Jimmy D Contr 74 MDSS/SGSI]

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?

Re: [PHP-DB] Query returns duplicate rows

[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

[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

[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

[PHP-DB] query problem..

[Micah Stevens]

Hi,

I'm getting an unknown colum `num` in where clause error with this query:

$options = mysql_query(select options.*, 
count(option_items.optionID) as `num` 
from options
 left join option_links using (optionID)
left join option_items on options.optionID = 
option_items.optionID
 where option_links.productID = '$productID'
and `num`  0);

But I'm defining it with the agregate function in the second line.. Any idea 
why it's popping the error?

-Micah

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

[PHP-DB] Query help

[Craig Hoffman]

Hey Everyone,
I can use some help here  I have query where one can selects an 
style then a area and finally rating through a drop menu.  When 
some selects a rating they select a range of ratings.  For example:

Style:  Traditional
Area:  Yosemite
Rating: From: 5.5 To: 5.10c
This should pull up all the rock climbs that are in Yosemite, that are 
traditional style and are between the rating 5.5 to 5.10c.  Here is my 
query:

SELECT * FROM routes, users WHERE area='$area' AND style='$style'  
BETWEEN rating='[$rating1]' AND rating='[$rating2]' GROUP BY route 
ORDER BY rating ASC ;

 For some reason which I am not seeing, this query is not doing what it 
should be doing.  Does anyone have any suggestions?

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

[PHP-DB] Query: Top 3 Results grouped by Category

[John Van Vranken]

Hi all,
I am working on a search result query in which the user types a keyword 
and the top 3 results for each category are displayed with 24 or so 
total results on the page.  The database is large (8 GB) and the table 
in which the fulltext search occurs has 3 million rows.
Tables:
product
category
category_link

The category table contains many categories but I want to output only 
top level general categories.  Each product has a link to its general 
category in the category_link table.

The fulltext search runs fine if I don't attempt to order and group the 
output by categories.  Currently I have 1 query that gets the top level 
categories (about 20 in all) then it loops through in PHP executing a 
fulltext search on EACH of those results.  This probably isn't the best 
solution since it requires executing a fulltext search 20 different 
times and in some cases (especially general words or multiword 
searches) takes much too long.   (anywhere from 2 - 45 seconds)

Is there a way to combine this into 1 FAST query?  I'm using MySQL 
4.0.17 and PHP 4.3.4 so I don't think subqueries are an option... I 
suppose I could upgrade if it was totally necessary.

The desired results for searching for Caffeine would look like this:
--
Search results for Caffeine
Drinks
- Caffeine Free Coke
- Caffeine Free Pepsi
- Jolt
Health Products
- NoDoz
- Generic Caffeine Tablets
- Caffeine Free Sleepytime Tea
Books
- Conquering Caffeine Dependence
- Understanding Caffeine : A Biobehavioral Analysis
- Over-the-Counter Drug Index 2004
Music
-DJ Micro - Caffeine: The Natural Stimulant
-Magnet - Caffeine Superstar

After each category I have a link to See all Matches in Category XYZ 
but that query runs fine since the dataset to search is reduced in size 
to just that particular category.

The simplified pseudo-code of what I am currently using is this:
GET TOP LEVEL CATEGORIES AND PLACE IN ARRAYS ( I left this code out)
// the resulting arrays might look like this:
$category_id = array(1,2,3);
$category_name = array(One,Two,Three);
for($i=0;$icount($category_id);$i++){
   $query = SELECT P.product_id, P.product_name,...other stuff...
   FROM product AS P, category AS C, category_link AS CL
   INNER JOIN ... join necessary tables for images etc...
   WHERE MATCH (P.product_name) AGAINST (' . $search_term . ') AND 
CL.product_id = P.product_id AND CL.category_id = ' . $category_id[$i] 
. '
   LIMIT 0, 3 ;
   $result = mysql_query($query);
   print() // output the 3 results
   print(See all matches in  . $category_name[$i]);
}

Thanks for any help!!!
- John

[PHP-DB] Query for Most Recent Entry

[Thompson, Jimi]

Hi,

 

I'm trying to figure out how to write a MySQL query that will return the
highest primary key a table.  

 

Thanks,

 

Ms. Jimi Thompson, CISSP

Manager, Web Operations

Cox School of Business

Southern Methodist University

 

What kind of peace do we seek? Not a 'Pax Americana' enforced on the
world by American weapons of war. Not the peace of the grave or the
security of a slave. I am talking about genuine peace, the kind of peace
that makes life on earth worth living, the kind that enables men and
nations to grow and to hope and to build a better life for their
children-not merely peace for Americans, but peace for all men and
women; not merely peace in our time, but peace for all time. - John F.
Kennedy

 

 

 

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

[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

[PHP-DB] Query Help!

[JeRRy]

Hi Everyone, Happy Easter to you all.

I have this query, reasoning for posting on this
Mailing List is under it. :)

SELECT `username` , `score` FROM round .
$round_number .  WHERE 1
ORDER BY `score` DESC LIMIT 0, 30

Is there a way to make it output the $round_number -1
(minus one number)?

I need this to be a temp. meassure as I only need the
number previous to the current $round_number for a few
days.  Is there a way in the query I can subtrack the
value by 1 and change it later when I want to show the
current round?

If it's too much hassles I can add $prev_round_number
and change the values each week but thought there
might be another way.

J

Find local movie times and trailers on Yahoo! Movies.
http://au.movies.yahoo.com

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

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

[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

[PHP-DB] query repeating results???

[Katie Dewees]

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

(not sure if the JOIN is correct at all...I usually just join with the WHERE
clauses, but I need to make sure that I get properties even when they do not
have a photo - maybe this should be an LEFT OUTER JOIN?)

properties is the main table (contains rental properties).

states contains the names and codes of states. The properties table
references the states table by an entry called state_id that, oddly
enough, contains the id number from the states table.

property_photos contains photos of the properties. There can be many
property_photo entries for each property. property_photos references the
properties table by the field property_id. In this case, I only want one
of the property photos returned...the one with property_photos.ordinal=1
(the one the customer ordered as first). I didn't fool with this right now,
though, because there are NO photos in the database right now.

This code is supposed to be searching for properties (think rental
properties) that are in a particular state (florida in this case). The state
is sent to the search program as a state code (fl in this case), not the
id number of the state in the state table (I did this to make it look more
readable to the user if seen in the query string - i.e. search.php?state=fl
instead of state=10).

There are only 7 properties entered into the database, but I am getting 10
results. It's giving me properties with id's 1-7, and then starting over
again and giving me id's 1-3.

All 7 should be listed as results since all 7 of them are in florida...but
why is it repeating the result set and showing 10 results? When I take the
LIMIT clause out, it gives me 315 results...the same repeating pattern
again.

It's late, and table joins confuse me...could someone please help? Please
let me know if I should supply more information - I've tried to be thorough,
but it's after midnight, and I need sleep. :-P

Katie Dewees
Web Developer
E-mail: [EMAIL PROTECTED]

-- 
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

[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] Query

[peppe]

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

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

[PHP-DB] Re: php-db Query

[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

[PHP-DB] Query to Array to Echo Problem

[Karen Resplendo]

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.

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

[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

[PHP-DB] Query Sum problem

[Larry Sandwick]

 

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 .

All information is in 1 table.

 

Original query

select distinct(Company_name), account, City, State from table where
number = 100

 

Failed miserably 

select distinct(Company_name) , account, City, State, sum(cost) from
table where number= 100 and status=HELD 

 

I need to be able to pull the $$ amount totals by company name for
status equal to HELD and OPEN .

 

Result 

 

 Company A  OPEN   HELD

 1233100.0050.00

 

Any suggestion would be appreciated.

 

Larry Sandwick

Sarreid, Ltd.

Network/System Administrator

phone: (252) 291-1414 x223

fax  : (252) 237-1592

 

Re: [PHP-DB] Query Sum problem

[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

[PHP-DB] Query Problem

[Larry Sandwick]

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 .

 

All information is in 1 table.

 

Original query

select distinct(Company_name), account, City, State from table where
number = 100

 

I need to be able to pull the $$ amount totals by company name for
status equal to HELD and OPEN .

 

Result 

 

 Company A  OPEN   HELD

 

 1233100.0050.00

 

 

Any suggestion would be appreciated.

 

 

 

Larry Sandwick

 

 

Larry Sandwick

Sarreid, Ltd.

Network/System Administrator

phone: (252) 291-1414 x223

fax  : (252) 237-1592

 

Re: [PHP-DB] Query Sum problem

[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

[PHP-DB] Query and math

[Patrik Fomin]

Hi,

q1)
i got a database with 2 diffrent tables,
i want to take out all the information that arent duplicated from
on of the tables, like this:

$sql = SELECT t1.rubrik, t1.info, t2.priser FROM table1 AS t1, table2 AS t2
WHERE t1.arkiv = '0' ORDER BY t1.rubrik;

the problem is that i got alot of duplicates (its for another purpose), so i
need to filter out the duplicates in the rubrik field to get just one match
each, like:

id - rubrik - info

0 -  - some info
1 -  - some info
2 -  - some info
3 -  - some info
4 -  - some info

would print out
0 -  - some info
1 -  - some info
2 -  - some info
3 -  - some info


q2)
i got like 100 matches from a database into a $num variable,
then i want to devide that by 3 aslong as it can be done, eg:

while ($num != 0) {

ïf ($num / 3 = true){
some code to display some fields from a database

$num = $num - 3;
}
else {
some other code to display
$num = 0;
}
}

basicly i want the if statement to check if its more then 3 posts left to
print out, if not its gonna print out the remaining last 1 - 2 post in a
diffrent way (for the looks)


regards
patrick

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

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

[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

[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

[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

[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

[PHP-DB] Query Case In-sensitive

[Larry Sandwick]

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