Re: [PHP-DB] mysql query
$sql2 = "insert into Inventory (UPC, quant, manuf, item, orderpt, ordrpt_flag, stock) values ('$upc', '$qnt','$mnf','$itm', '$odrpt', 0, $stk)"; Looks like, you have the ' ' missing for $qnt and odrpt had the $ and the '' missing. The above query should work, I haven't tested it though. Also for no error, have you turned off the PHP error reporting? It probably is working on the command line, since you posted actual values there and in the PHP code, you are using the variables which I think were not wrapped in the query correctly? On Thu, Aug 22, 2013 at 12:51 PM, Michael Oki wrote: > Try the insertion like this: > $sql2 = mysql_query("insert into Inventory (`UPC` > , `quant`, `manuf`, `item`, `orderpt`, `ordrpt_flag`, `stock`)" > ."values ('$upc', $qnt,'$mnf','$itm', > '$odrpt', '0', '$stk') " ) or die(mysql_error()); > > On 22 August 2013 05:10, Daniel Krook wrote: > > > Ethan, > > > > What about: > > > > $result2 = mysqli_query(cxn, $sql2); > > > > Doesn't look like you're sending it a connection link as a variable > ($cxn) > > and that's passed through as a literal? > > > > > > > > > > Thanks, > > > > > > Daniel Krook > > Software Engineer, Advanced Cloud Solutions, GTS > > > > IBM Senior Certified IT Specialist - L3 Thought Leader > > The Open Group Certified IT Specialist - L3 Distinguished > > Cloud, Java, PHP, BlackBerry, DB2 & Solaris Certified > > > > > > > > > > > > > > Ethan Rosenberg wrote on 08/21/2013 > > 11:59:19 PM: > > > > > From: Ethan Rosenberg > > > To: Daniel Krook/White Plains/IBM@IBMUS > > > Cc: PHP Database List > > > Date: 08/21/2013 11:59 PM > > > Subject: Re: [PHP-DB] mysql query > > > > > > On 08/21/2013 11:30 PM, Daniel Krook wrote: > > > Ethan, > > > > > > It's hard to tell from the code formatting in your email what the > > > exact problem might be, but a few reasons that this might fail in > > > PHP rather than when sent to MySQL with hardcoded values: > > > > > > 1. var_dump/print_r $_POST to see what you're getting as input is > > > what you expect (and sanitize!). > > > > > > 2. Check that the SQL statement concatenation in PHP is building > > > the string you're expecting. It looks like you're joining 2 strings > > > when defining $sql2 that doesn't leave a space between the close > > > parentheses and "values." Compare this against what you're sending > > > "on the command line." > > > > > > 3. Get rid of all single quotes... escape your double quotes where > > > needed. This will avoid any variable-in-string interpolation errors > > > and may help you find the issue with input data. Same with your echo > > > $sql2 statement... that's not going to give you the same thing as > > > the print_r below it. > > > > > > > > > > > > Thanks, > > > > > > > > > Daniel Krook > > > Software Engineer, Advanced Cloud Solutions, GTS > > > > > > IBM Senior Certified IT Specialist - L3 Thought Leader > > > The Open Group Certified IT Specialist - L3 Distinguished > > > Cloud, Java, PHP, BlackBerry, DB2 & Solaris Certified > > > > > > > > > > > > > > > Ethan Rosenberg wrote on 08/21/ > > > 2013 07:48:12 PM: > > > > > > > From: Ethan Rosenberg > > > > To: PHP Database List > > > > Date: 08/21/2013 07:48 PM > > > > Subject: [PHP-DB] mysql query > > > > > > > > Dear List - > > > > > > > > I can't figure this out > > > > > > > > mysql> describe Inventory; > > > > +-+-+--+-+-+---+ > > > > | Field | Type| Null | Key | Default | Extra | > > > > +-+-+--+-+-+---+ > > > > | UPC | varchar(14) | YES | | NULL | | > > > > | quant | int(5) | NO | | NULL | | > > > > | manuf | varchar(20) | YES | | NULL | | > > > > | item| varchar(50) | YES | | NULL | | > > > > | orderpt | tinyint(4)
Re: [PHP-DB] mysql query
Try the insertion like this: $sql2 = mysql_query("insert into Inventory (`UPC` , `quant`, `manuf`, `item`, `orderpt`, `ordrpt_flag`, `stock`)" ."values ('$upc', $qnt,'$mnf','$itm', '$odrpt', '0', '$stk') " ) or die(mysql_error()); On 22 August 2013 05:10, Daniel Krook wrote: > Ethan, > > What about: > > $result2 = mysqli_query(cxn, $sql2); > > Doesn't look like you're sending it a connection link as a variable ($cxn) > and that's passed through as a literal? > > > > > Thanks, > > > Daniel Krook > Software Engineer, Advanced Cloud Solutions, GTS > > IBM Senior Certified IT Specialist - L3 Thought Leader > The Open Group Certified IT Specialist - L3 Distinguished > Cloud, Java, PHP, BlackBerry, DB2 & Solaris Certified > > > > > > > Ethan Rosenberg wrote on 08/21/2013 > 11:59:19 PM: > > > From: Ethan Rosenberg > > To: Daniel Krook/White Plains/IBM@IBMUS > > Cc: PHP Database List > > Date: 08/21/2013 11:59 PM > > Subject: Re: [PHP-DB] mysql query > > > > On 08/21/2013 11:30 PM, Daniel Krook wrote: > > Ethan, > > > > It's hard to tell from the code formatting in your email what the > > exact problem might be, but a few reasons that this might fail in > > PHP rather than when sent to MySQL with hardcoded values: > > > > 1. var_dump/print_r $_POST to see what you're getting as input is > > what you expect (and sanitize!). > > > > 2. Check that the SQL statement concatenation in PHP is building > > the string you're expecting. It looks like you're joining 2 strings > > when defining $sql2 that doesn't leave a space between the close > > parentheses and "values." Compare this against what you're sending > > "on the command line." > > > > 3. Get rid of all single quotes... escape your double quotes where > > needed. This will avoid any variable-in-string interpolation errors > > and may help you find the issue with input data. Same with your echo > > $sql2 statement... that's not going to give you the same thing as > > the print_r below it. > > > > > > > > Thanks, > > > > > > Daniel Krook > > Software Engineer, Advanced Cloud Solutions, GTS > > > > IBM Senior Certified IT Specialist - L3 Thought Leader > > The Open Group Certified IT Specialist - L3 Distinguished > > Cloud, Java, PHP, BlackBerry, DB2 & Solaris Certified > > > > > > > > > > Ethan Rosenberg wrote on 08/21/ > > 2013 07:48:12 PM: > > > > > From: Ethan Rosenberg > > > To: PHP Database List > > > Date: 08/21/2013 07:48 PM > > > Subject: [PHP-DB] mysql query > > > > > > Dear List - > > > > > > I can't figure this out > > > > > > mysql> describe Inventory; > > > +-+-+--+-+-+---+ > > > | Field | Type| Null | Key | Default | Extra | > > > +-+-+--+-+-+---+ > > > | UPC | varchar(14) | YES | | NULL | | > > > | quant | int(5) | NO | | NULL | | > > > | manuf | varchar(20) | YES | | NULL | | > > > | item| varchar(50) | YES | | NULL | | > > > | orderpt | tinyint(4) | NO | | NULL | | > > > | ordrpt_flag | tinyint(3) | YES | | NULL | | > > > | stock | int(3) | YES | | NULL | | > > > +-+-+--+-+-+---+ > > > > > > Here are code snippets - > > > > > >$upc = $_SESSION['UPC']; > > >$qnt = $_POST['quant']; > > >$mnf = $_POST['manuf']; > > >$itm = $_POST['item']; > > >$odrpt = $_POST['oderpt']; > > >$opf = $_POST['ordrpt_flag']; > > >$stk= $_POST['stock']; > > > > > >$sql2 = "insert into Inventory (UPC, quant, > > > > manuf, item, orderpt, ordrpt_flag, stock)" > > > ."values ('$upc', $qnt,'$mnf','$itm', > > > > odrpt, 0, $stk)"; > > >$result2 = mysqli_query(cxn, $sql2); > > >echo '$sql2'; > > >print_r($sql2); > > >echo "$upc $qnt $mnf $itm $odrpt $opf > > > > $stk"; > > >if (!$result2) > > > die('Could not enter data: ' . > > > mysqli_error()); > > > > > > The mysql query fails. I cannot figure out why. It works from the > > > command line. > > > > > > TIA > > > > > > Ethan > > > > > Daniel - > > > > Thanks. > > > > Tried all your suggestions. > > > > Sorry, no luck. > > > > Ethan
Re: [PHP-DB] mysql query
Ethan, What about: $result2 = mysqli_query(cxn, $sql2); Doesn't look like you're sending it a connection link as a variable ($cxn) and that's passed through as a literal? Thanks, Daniel Krook Software Engineer, Advanced Cloud Solutions, GTS IBM Senior Certified IT Specialist - L3 Thought Leader The Open Group Certified IT Specialist - L3 Distinguished Cloud, Java, PHP, BlackBerry, DB2 & Solaris Certified Ethan Rosenberg wrote on 08/21/2013 11:59:19 PM: > From: Ethan Rosenberg > To: Daniel Krook/White Plains/IBM@IBMUS > Cc: PHP Database List > Date: 08/21/2013 11:59 PM > Subject: Re: [PHP-DB] mysql query > > On 08/21/2013 11:30 PM, Daniel Krook wrote: > Ethan, > > It's hard to tell from the code formatting in your email what the > exact problem might be, but a few reasons that this might fail in > PHP rather than when sent to MySQL with hardcoded values: > > 1. var_dump/print_r $_POST to see what you're getting as input is > what you expect (and sanitize!). > > 2. Check that the SQL statement concatenation in PHP is building > the string you're expecting. It looks like you're joining 2 strings > when defining $sql2 that doesn't leave a space between the close > parentheses and "values." Compare this against what you're sending > "on the command line." > > 3. Get rid of all single quotes... escape your double quotes where > needed. This will avoid any variable-in-string interpolation errors > and may help you find the issue with input data. Same with your echo > $sql2 statement... that's not going to give you the same thing as > the print_r below it. > > > > Thanks, > > > Daniel Krook > Software Engineer, Advanced Cloud Solutions, GTS > > IBM Senior Certified IT Specialist - L3 Thought Leader > The Open Group Certified IT Specialist - L3 Distinguished > Cloud, Java, PHP, BlackBerry, DB2 & Solaris Certified > > > > > Ethan Rosenberg wrote on 08/21/ > 2013 07:48:12 PM: > > > From: Ethan Rosenberg > > To: PHP Database List > > Date: 08/21/2013 07:48 PM > > Subject: [PHP-DB] mysql query > > > > Dear List - > > > > I can't figure this out > > > > mysql> describe Inventory; > > +-+-+--+-+-+---+ > > | Field | Type| Null | Key | Default | Extra | > > +-+-+--+-+-+---+ > > | UPC | varchar(14) | YES | | NULL | | > > | quant | int(5) | NO | | NULL | | > > | manuf | varchar(20) | YES | | NULL | | > > | item| varchar(50) | YES | | NULL | | > > | orderpt | tinyint(4) | NO | | NULL | | > > | ordrpt_flag | tinyint(3) | YES | | NULL | | > > | stock | int(3) | YES | | NULL | | > > +-+-+--+-+-+---+ > > > > Here are code snippets - > > > >$upc = $_SESSION['UPC']; > >$qnt = $_POST['quant']; > >$mnf = $_POST['manuf']; > >$itm = $_POST['item']; > >$odrpt = $_POST['oderpt']; > >$opf = $_POST['ordrpt_flag']; > >$stk= $_POST['stock']; > > > >$sql2 = "insert into Inventory (UPC, quant, > > manuf, item, orderpt, ordrpt_flag, stock)" > > ."values ('$upc', $qnt,'$mnf','$itm', > > odrpt, 0, $stk)"; > >$result2 = mysqli_query(cxn, $sql2); > >echo '$sql2'; > >print_r($sql2); > >echo "$upc $qnt $mnf $itm $odrpt $opf > > $stk"; > >if (!$result2) > > die('Could not enter data: ' . > > mysqli_error()); > > > > The mysql query fails. I cannot figure out why. It works from the > > command line. > > > > TIA > > > > Ethan > > > Daniel - > > Thanks. > > Tried all your suggestions. > > Sorry, no luck. > > Ethan
Re: [PHP-DB] mysql query
Ethan, It's hard to tell from the code formatting in your email what the exact problem might be, but a few reasons that this might fail in PHP rather than when sent to MySQL with hardcoded values: 1. var_dump/print_r $_POST to see what you're getting as input is what you expect (and sanitize!). 2. Check that the SQL statement concatenation in PHP is building the string you're expecting. It looks like you're joining 2 strings when defining $sql2 that doesn't leave a space between the close parentheses and "values." Compare this against what you're sending "on the command line." 3. Get rid of all single quotes... escape your double quotes where needed. This will avoid any variable-in-string interpolation errors and may help you find the issue with input data. Same with your echo $sql2 statement... that's not going to give you the same thing as the print_r below it. Thanks, Daniel Krook Software Engineer, Advanced Cloud Solutions, GTS IBM Senior Certified IT Specialist - L3 Thought Leader The Open Group Certified IT Specialist - L3 Distinguished Cloud, Java, PHP, BlackBerry, DB2 & Solaris Certified Ethan Rosenberg wrote on 08/21/2013 07:48:12 PM: > From: Ethan Rosenberg > To: PHP Database List > Date: 08/21/2013 07:48 PM > Subject: [PHP-DB] mysql query > > Dear List - > > I can't figure this out > > mysql> describe Inventory; > +-+-+--+-+-+---+ > | Field | Type| Null | Key | Default | Extra | > +-+-+--+-+-+---+ > | UPC | varchar(14) | YES | | NULL | | > | quant | int(5) | NO | | NULL | | > | manuf | varchar(20) | YES | | NULL | | > | item| varchar(50) | YES | | NULL | | > | orderpt | tinyint(4) | NO | | NULL | | > | ordrpt_flag | tinyint(3) | YES | | NULL | | > | stock | int(3) | YES | | NULL | | > +-+-+--+-+-+---+ > > Here are code snippets - > >$upc = $_SESSION['UPC']; >$qnt = $_POST['quant']; >$mnf = $_POST['manuf']; >$itm = $_POST['item']; >$odrpt = $_POST['oderpt']; >$opf = $_POST['ordrpt_flag']; >$stk= $_POST['stock']; > >$sql2 = "insert into Inventory (UPC, quant, > manuf, item, orderpt, ordrpt_flag, stock)" > ."values ('$upc', $qnt,'$mnf','$itm', > odrpt, 0, $stk)"; >$result2 = mysqli_query(cxn, $sql2); >echo '$sql2'; >print_r($sql2); >echo "$upc $qnt $mnf $itm $odrpt $opf > $stk"; >if (!$result2) > die('Could not enter data: ' . > mysqli_error()); > > The mysql query fails. I cannot figure out why. It works from the > command line. > > TIA > > Ethan > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php >
Re: [PHP-DB] mysql query
On 08/21/2013 07:52 PM, Toby Hart Dyke wrote: 1) What is the error message? 2) This has an error: >values ('$upc', $qnt,'$mnf','$itm', odrpt, 0, $stk) Missing '$' in front of 'odrpt'. Toby On 8/22/2013 12:48 AM, Ethan Rosenberg wrote: Dear List - I can't figure this out mysql> describe Inventory; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | UPC | varchar(14) | YES | | NULL | | | quant | int(5) | NO | | NULL | | | manuf | varchar(20) | YES | | NULL | | | item| varchar(50) | YES | | NULL | | | orderpt | tinyint(4) | NO | | NULL | | | ordrpt_flag | tinyint(3) | YES | | NULL | | | stock | int(3) | YES | | NULL | | +-+-+--+-+-+---+ Here are code snippets - $upc = $_SESSION['UPC']; $qnt = $_POST['quant']; $mnf = $_POST['manuf']; $itm = $_POST['item']; $odrpt = $_POST['oderpt']; $opf = $_POST['ordrpt_flag']; $stk= $_POST['stock']; $sql2 = "insert into Inventory (UPC, quant, manuf, item, orderpt, ordrpt_flag, stock)" ."values ('$upc', $qnt,'$mnf','$itm', odrpt, 0, $stk)"; $result2 = mysqli_query(cxn, $sql2); echo '$sql2'; print_r($sql2); echo "$upc $qnt $mnf $itm $odrpt $opf $stk"; if (!$result2) die('Could not enter data: ' . mysqli_error()); The mysql query fails. I cannot figure out why. It works from the command line. TIA Ethan Toby - The problem is that I do not get any error messages. From this if (!$result2) die('Could not enter data: ' . mysqli_error()); I only get the 'Could not enter data: and no error message. Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] mysql query
1) What is the error message? 2) This has an error: >values ('$upc', $qnt,'$mnf','$itm', odrpt, 0, $stk) Missing '$' in front of 'odrpt'. Toby On 8/22/2013 12:48 AM, Ethan Rosenberg wrote: Dear List - I can't figure this out mysql> describe Inventory; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | UPC | varchar(14) | YES | | NULL | | | quant | int(5) | NO | | NULL | | | manuf | varchar(20) | YES | | NULL | | | item| varchar(50) | YES | | NULL | | | orderpt | tinyint(4) | NO | | NULL | | | ordrpt_flag | tinyint(3) | YES | | NULL | | | stock | int(3) | YES | | NULL | | +-+-+--+-+-+---+ Here are code snippets - $upc = $_SESSION['UPC']; $qnt = $_POST['quant']; $mnf = $_POST['manuf']; $itm = $_POST['item']; $odrpt = $_POST['oderpt']; $opf = $_POST['ordrpt_flag']; $stk= $_POST['stock']; $sql2 = "insert into Inventory (UPC, quant, manuf, item, orderpt, ordrpt_flag, stock)" ."values ('$upc', $qnt,'$mnf','$itm', odrpt, 0, $stk)"; $result2 = mysqli_query(cxn, $sql2); echo '$sql2'; print_r($sql2); echo "$upc $qnt $mnf $itm $odrpt $opf $stk"; if (!$result2) die('Could not enter data: ' . mysqli_error()); The mysql query fails. I cannot figure out why. It works from the command line. TIA Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] mysql query
Dear List - I can't figure this out mysql> describe Inventory; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | UPC | varchar(14) | YES | | NULL | | | quant | int(5) | NO | | NULL | | | manuf | varchar(20) | YES | | NULL | | | item| varchar(50) | YES | | NULL | | | orderpt | tinyint(4) | NO | | NULL | | | ordrpt_flag | tinyint(3) | YES | | NULL | | | stock | int(3) | YES | | NULL | | +-+-+--+-+-+---+ Here are code snippets - $upc = $_SESSION['UPC']; $qnt = $_POST['quant']; $mnf = $_POST['manuf']; $itm = $_POST['item']; $odrpt = $_POST['oderpt']; $opf = $_POST['ordrpt_flag']; $stk= $_POST['stock']; $sql2 = "insert into Inventory (UPC, quant, manuf, item, orderpt, ordrpt_flag, stock)" ."values ('$upc', $qnt,'$mnf','$itm', odrpt, 0, $stk)"; $result2 = mysqli_query(cxn, $sql2); echo '$sql2'; print_r($sql2); echo "$upc $qnt $mnf $itm $odrpt $opf $stk"; if (!$result2) die('Could not enter data: ' . mysqli_error()); The mysql query fails. I cannot figure out why. It works from the command line. TIA Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Mysql query
Chris I spent 3 hours debugging this query myself. I got as far as putting '' around 2009-12-25 to get the desired results. I just added the word DATE. It works, thanks. Chris I run a verse of the day e-mail list. This query determines the logic of the content (year round, Easter and Christmas). It is quite the query to say the least. Thanks for your help. Sincerely, Ron www.TheVerseOfTheDay.info > ron.pigg...@actsministries.org wrote: >> The query from my previous post was only part of a larger query. This >> is >> the entire query: >> >> SELECT GREATEST( IF( CURDATE( ) >= DATE_SUB( DATE( FROM_UNIXTIME( >> 1239508800 ) ) , INTERVAL LEAST( 14, ( >> >> SELECT COUNT( * ) >> FROM `verse_of_the_day_Bible_verses` >> WHERE seasonal_use =1 ) ) >> DAY ) >> AND CURDATE( ) <= DATE( FROM_UNIXTIME( 1239508800 ) ) , 1, 0 ) , IF( >> CURDATE( ) >= DATE_SUB( DATE( 2009 -12 -25 ) , INTERVAL LEAST( 14, ( >> >> SELECT COUNT( * ) >> FROM `verse_of_the_day_Bible_verses` >> WHERE seasonal_use =2 ) ) >> DAY ) >> AND CURDATE( ) <= DATE( 2009 -12 -25 ) , 2, 0 >> ) >> ) AS verse_application > > It took me a while to work out what this was trying to do, that's > complicated. > > Reformatted a little: > > SELECT >GREATEST( > IF > ( >CURDATE() >= > DATE_SUB( > DATE(FROM_UNIXTIME(1239508800)), > INTERVAL LEAST(14, (SELECT 1)) DAY) > AND CURDATE() <= DATE(FROM_UNIXTIME(1239508800)), >1, >0 > ), > IF > ( > CURDATE() >= > DATE_SUB( > DATE('2009-12-25'), > INTERVAL LEAST(14, (SELECT 2)) DAY) >AND CURDATE() <= DATE('2009-12-25'), > 2, > 0 > ) >) AS verse_application; > > (which isn't much better in email). > > You're not getting '2' because the second part is returning 0. > > I substituted dummy variables for your subqueries (select 1 and select 2). > > SELECT COUNT( * ) > FROM `verse_of_the_day_Bible_verses` > WHERE seasonal_use =2; > > What does that return by itself? > > that is what your query will run instead of my 'select 2'. > > That in turn goes into the > > select least(14, result_from_above_query); > > and takes that away from date('2009-12-25'); > > If the current date is not in that range, it will return 0. > > Here's the second part of your query isolated for you to test: > > SELECT >IF > ( >CURDATE() >= > DATE_SUB( >DATE('2009-12-25'), >INTERVAL LEAST(14, (SELECT COUNT(*) FROM > verse_of_the_day_Bible_verses WHERE seasonal_use=2)) DAY) > AND CURDATE() <= DATE('2009-12-25'), > 2, > 0 >) > ; > > > -- > 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] Mysql query
ron.pigg...@actsministries.org wrote: The query from my previous post was only part of a larger query. This is the entire query: SELECT GREATEST( IF( CURDATE( ) >= DATE_SUB( DATE( FROM_UNIXTIME( 1239508800 ) ) , INTERVAL LEAST( 14, ( SELECT COUNT( * ) FROM `verse_of_the_day_Bible_verses` WHERE seasonal_use =1 ) ) DAY ) AND CURDATE( ) <= DATE( FROM_UNIXTIME( 1239508800 ) ) , 1, 0 ) , IF( CURDATE( ) >= DATE_SUB( DATE( 2009 -12 -25 ) , INTERVAL LEAST( 14, ( SELECT COUNT( * ) FROM `verse_of_the_day_Bible_verses` WHERE seasonal_use =2 ) ) DAY ) AND CURDATE( ) <= DATE( 2009 -12 -25 ) , 2, 0 ) ) AS verse_application It took me a while to work out what this was trying to do, that's complicated. Reformatted a little: SELECT GREATEST( IF ( CURDATE() >= DATE_SUB( DATE(FROM_UNIXTIME(1239508800)), INTERVAL LEAST(14, (SELECT 1)) DAY) AND CURDATE() <= DATE(FROM_UNIXTIME(1239508800)), 1, 0 ), IF ( CURDATE() >= DATE_SUB( DATE('2009-12-25'), INTERVAL LEAST(14, (SELECT 2)) DAY) AND CURDATE() <= DATE('2009-12-25'), 2, 0 ) ) AS verse_application; (which isn't much better in email). You're not getting '2' because the second part is returning 0. I substituted dummy variables for your subqueries (select 1 and select 2). SELECT COUNT( * ) FROM `verse_of_the_day_Bible_verses` WHERE seasonal_use =2; What does that return by itself? that is what your query will run instead of my 'select 2'. That in turn goes into the select least(14, result_from_above_query); and takes that away from date('2009-12-25'); If the current date is not in that range, it will return 0. Here's the second part of your query isolated for you to test: SELECT IF ( CURDATE() >= DATE_SUB( DATE('2009-12-25'), INTERVAL LEAST(14, (SELECT COUNT(*) FROM verse_of_the_day_Bible_verses WHERE seasonal_use=2)) DAY) AND CURDATE() <= DATE('2009-12-25'), 2, 0 ) ; -- 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] Mysql query
The query from my previous post was only part of a larger query. This is the entire query: SELECT GREATEST( IF( CURDATE( ) >= DATE_SUB( DATE( FROM_UNIXTIME( 1239508800 ) ) , INTERVAL LEAST( 14, ( SELECT COUNT( * ) FROM `verse_of_the_day_Bible_verses` WHERE seasonal_use =1 ) ) DAY ) AND CURDATE( ) <= DATE( FROM_UNIXTIME( 1239508800 ) ) , 1, 0 ) , IF( CURDATE( ) >= DATE_SUB( DATE( 2009 -12 -25 ) , INTERVAL LEAST( 14, ( SELECT COUNT( * ) FROM `verse_of_the_day_Bible_verses` WHERE seasonal_use =2 ) ) DAY ) AND CURDATE( ) <= DATE( 2009 -12 -25 ) , 2, 0 ) ) AS verse_application The result should be a "2". I am getting a 0. When I try the first subquery / IF statement the error message is: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF( CURDATE( ) >= DATE_SUB( DATE( FROM_UNIXTIME(1239508800) ) , INTERVAL LEAST( ' The error message for the Christmas check which should be giving me a "2" result is: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF( CURDATE( ) >= DATE_SUB( DATE( 2009 -12 -25 ) , INTERVAL LEAST( 14, ( SELE' at line 1 Any help out there please? Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Mysql query
Does anyone see anything wrong with this query? Do I have one of the >, = or < mixed up? The purpose is to figure out if it is within 14 days of Christmas AND if there is content for Christmas available. 2 is symbolic in the database being Christmas. Ron IF( CURDATE( ) >= DATE_SUB( DATE(2009-12-25) , INTERVAL LEAST( 14, ( SELECT COUNT( * ) FROM `verse_of_the_day_Bible_verses` WHERE seasonal_use =2 ) ) DAY ) AND CURDATE( ) <= DATE(2009-12-25) , 2, 0 ) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] mySQL query syntax error
I found the problem. The first subquery needed "AS" aliases. I am learning more of what mySQL is capable of and appreciate the help. Ron -Original Message- From: TG To: ron.pigg...@actsministries.org Subject: Re: [PHP-DB] mySQL query syntax error Date: Sat, 14 Nov 2009 07:17:53 -0500 Only half awake and don't really see the problem with customers.email since you alias the two selects and union as 'customers'. But since you don't have any other tables in your primary select, you can drop the `customers` part and just say "ORDER BY email ASC". - Original Message - From: Ron Piggott To: PHP DB Date: Sat, 14 Nov 2009 03:23:47 -0500 Subject: [PHP-DB] mySQL query syntax error > > I am getting the following error message: > > #1054 - Unknown column 'customers.email' in 'order clause' > > from the query below --- I don't understand why. Would someone help me > please? Ron > > > SELECT 'first_name', 'last_name', 'email' > FROM ( > > ( > > SELECT `firstname` , `lastname` , `buyer_email` > FROM `paypal_payment_info` > WHERE `datecreation` = '$two_weeks_ago' > GROUP BY `buyer_email` > ) > UNION ALL ( > > SELECT `mail_order_address`.`first_name` , > `mail_order_address`.`last_name` , `mail_order_address`.`email` > FROM `mail_order_address` > INNER JOIN `mail_order_payment` ON `mail_order_address`.`reference` = > `mail_order_payment`.`mail_order_address_reference` > WHERE `mail_order_payment`.`payment_received` = '$two_weeks_ago' > GROUP BY `mail_order_address`.`email` > ) > ) AS customers > ORDER BY `customers`.`email` ASC > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >
[PHP-DB] mySQL query syntax error
I am getting the following error message: #1054 - Unknown column 'customers.email' in 'order clause' from the query below --- I don't understand why. Would someone help me please? Ron SELECT 'first_name', 'last_name', 'email' FROM ( ( SELECT `firstname` , `lastname` , `buyer_email` FROM `paypal_payment_info` WHERE `datecreation` = '$two_weeks_ago' GROUP BY `buyer_email` ) UNION ALL ( SELECT `mail_order_address`.`first_name` , `mail_order_address`.`last_name` , `mail_order_address`.`email` FROM `mail_order_address` INNER JOIN `mail_order_payment` ON `mail_order_address`.`reference` = `mail_order_payment`.`mail_order_address_reference` WHERE `mail_order_payment`.`payment_received` = '$two_weeks_ago' GROUP BY `mail_order_address`.`email` ) ) AS customers ORDER BY `customers`.`email` ASC -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MySQL query executes outside of PHP, but not in PHP
> error_log("Hey, the SQL is: $sql"); > > Then look in your php error log (you do have error logging enabled, > right?) [McGovern, Dave] Enabled, yes. Usefully enabled, no (correcting this helped a lot). > > If that SQL in the error log is fine, then your problem is > $client2->multi_query($sql) -- what does THAT return? What SHOULD it > return? What are you expecting it to return? Does it return what you > thought it did? [McGovern, Dave] It was returning ''. I was expecting 1. > > When you do "if ($var)" it can sometimes have unexpected results. If $var > is an empty string, it's still true, and executes. I don't know that > multi_query SHOULD return, or how to determine if it throws an error, but > that's one place to start. > > Next, if multi_query worked, then this line is suspect: > >if ($result = $client2->use_result()) { > > This will always result in TRUE, as the assignment will always succeed. > Change it to: > >if (($result = $client2->use_result())) { > > (added parenthesis) > What DB library are you using? [McGovern, Dave] php_mysqli.dll >--- >Peter Beckman Internet Guy >beck...@angryox.com http://www.angryox.com/ >--- [McGovern, Dave] Hi, Peter - Thanks for the suggestions. I did have error logging enabled, but I wasn't writing any of my own errors to the log, so it wasn't doing me much good. Once I started doing this, I was able to trace the source of the error to the fact that this particular SQL script was meant to be aimed at a different schema. The reason it worked in MySQL Query Browser was that the required schema was already selected in the Query Browser (ouch). Sorry to have wasted the list's time with what turned out to be a silly human error. Dave -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL query executes outside of PHP, but not in PHP
On Fri, 23 Jan 2009, dave.mcgov...@sungard.com wrote: Hi, I am running: PHP 5.2.8, Apache 2.2.11, MySQL 5.1.30 on Win32/XP. I have a number of queries on my page which are very similar in structure, and they all work except for the following one. $mysql['process'] = $client2->real_escape_string($clean['process']); $sql = "SELECT f.name, f.description FROM files f, file_mapping m, processes p WHERE m.file_id = f.id AND p.name = '{$mysql['process']}' AND m.process_id = p.id AND m.io_flag = 'I'"; if ($client2->multi_query($sql)) { echo 'Main Input Files'; do { if ($result = $client2->use_result()) { while ($input = $result->fetch_row()) { $filename = $input[0]; $descr = $input[1]; echo ''.$filename.''.' '.$descr.''; } $result->close(); } } while ($client2->next_result()); } If I echo the $sql, and then run it in MySQL directly, it works fine. I have tried replacing the variable in the WHERE clause with a hardcoded value and and have tried replacing this query with a very basic query with no variable, but nothing has worked. No error message is returned. Any suggestions as to what I might check? Here's an example of an echo of the following $sql that runs OK in MySQL Query Browser: SELECT f.name, f.description FROM files f, file_mapping m, processes p WHERE m.file_id = f.id AND p.name = 'BCOM1AC' AND m.process_id = p.id AND m.io_flag = 'I' error_log("Hey, the SQL is: $sql"); Then look in your php error log (you do have error logging enabled, right?) If that SQL in the error log is fine, then your problem is $client2->multi_query($sql) -- what does THAT return? What SHOULD it return? What are you expecting it to return? Does it return what you thought it did? When you do "if ($var)" it can sometimes have unexpected results. If $var is an empty string, it's still true, and executes. I don't know that multi_query SHOULD return, or how to determine if it throws an error, but that's one place to start. Next, if multi_query worked, then this line is suspect: if ($result = $client2->use_result()) { This will always result in TRUE, as the assignment will always succeed. Change it to: if (($result = $client2->use_result())) { (added parenthesis) What DB library are you using? --- Peter Beckman Internet Guy beck...@angryox.com http://www.angryox.com/ --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL query executes outside of PHP, but not in PHP
Hi, I am running: PHP 5.2.8, Apache 2.2.11, MySQL 5.1.30 on Win32/XP. I have a number of queries on my page which are very similar in structure, and they all work except for the following one. $mysql['process'] = $client2->real_escape_string($clean['process']); $sql = "SELECT f.name, f.description FROM files f, file_mapping m, processes p WHERE m.file_id = f.id AND p.name = '{$mysql['process']}' AND m.process_id = p.id AND m.io_flag = 'I'"; if ($client2->multi_query($sql)) { echo 'Main Input Files'; do { if ($result = $client2->use_result()) { while ($input = $result->fetch_row()) { $filename = $input[0]; $descr = $input[1]; echo ''.$filename.''.' '.$descr.''; } $result->close(); } } while ($client2->next_result()); } If I echo the $sql, and then run it in MySQL directly, it works fine. I have tried replacing the variable in the WHERE clause with a hardcoded value and and have tried replacing this query with a very basic query with no variable, but nothing has worked. No error message is returned. Any suggestions as to what I might check? Here's an example of an echo of the following $sql that runs OK in MySQL Query Browser: SELECT f.name, f.description FROM files f, file_mapping m, processes p WHERE m.file_id = f.id AND p.name = 'BCOM1AC' AND m.process_id = p.id AND m.io_flag = 'I' Thanks, Dave -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL Query Timeout program in PHP
Man- when the query is fired through the web interface- it rans on mysql server max_execution_time - wont' help evn I stop apache itself... Yes it will. Apache will be instructed to stop execution of any script by the mod_php module (assuming you are using mod_php). But mysql process probably won't stop running (not 100% sure for that) although php script has timed out. The query runs on mysql server - so I have to kill the PID on server itself... In order to do this you will need (in the unix world) to have rights to kill mysql processes. That means that you must become either root or the mysql user which is not that simple since everything that apache runs is usually running as the apache or the www user who can't kill those processes (at least if he can't su exec). I will have to note here that this is a bad practice of doing things... Thanks Piyush On Thu, Oct 16, 2008 at 7:57 AM, Jack van Zanen <[EMAIL PROTECTED]> wrote: Just put the time out in your PHP.INI file max_execution_time = 30 ; Maximum execution time of each script, in seconds This is probably a solution although you will have to know what to expect if you query a table with 3M records. It will be slow and that can't change. Try using "LIMIT" when sending queries to stop mass data retrieval. 2008/10/16 Piyush Kumar <[EMAIL PROTECTED]> I'm using http://myclient.polarlava.com/ as web query interface for mysql server Now I want to add "Query Timeout" functionality to it Every apache process has a timeout limit you can leave the user wait for a page a lifetime. For that I need to get the PID for last ran mysql query and then using kill PID - I can kill the process on MySQL server Sorry but I still can't see why. Please explain how to do that in PHP Thanks! Similar to what described @ http://bytes.com/forum/thread156058.html -- Thanks & Regards, -Piyush Mo.: 091-9910904233 Mail: [EMAIL PROTECTED] Web: http://piyush.me/ -In a world without fences, limits, boundaries and walls, Who needs Windows and Gates? Although some think that limits are wrong I still have walls around my house. Windows are not so bad sometimes everything has its use. Besides everyone needs to be annoyed from time to time. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- J.A. van Zanen -- Thodoris
Re: [PHP-DB] MySQL Query Timeout program in PHP
Piyush Kumar wrote: show full processlist ; gives me whole query -- but I want some php function -- like mysql_info() -- to return the PID of last run query Please do not email me directly. Always send to the mailing list. If it's not listed under php.net/mysql somewhere, then it's not available as a native php function. -- 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] MySQL Query Timeout program in PHP
Piyush Kumar wrote: Hi Chris, As I mentioned in my query I want to Kill only the query ran by web client not all queries running on the server. The perl script kill all queries - which are been running from for last 120(or any threshold set) Issues: How do I get the PID of my last select query ran from the web query interface..?? Any php function..? No, there isn't. show processlist; should give you some info. It won't give you the whole query, only the start of it. -- 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] MySQL Query Timeout program in PHP
-- Forwarded message -- From: Piyush Kumar <[EMAIL PROTECTED]> Date: Thu, Oct 16, 2008 at 9:09 AM Subject: Re: [PHP-DB] MySQL Query Timeout program in PHP To: Chris <[EMAIL PROTECTED]> Hi Chris, As I mentioned in my query I want to Kill only the query ran by web client not all queries running on the server. The perl script kill all queries - which are been running from for last 120(or any threshold set) Issues: How do I get the PID of my last select query ran from the web query interface..?? Any php function..? Thanks Piyush On Thu, Oct 16, 2008 at 7:35 AM, Chris <[EMAIL PROTECTED]> wrote: > Piyush Kumar wrote: >> >> I'm using http://myclient.polarlava.com/ as web query interface for mysql >> server >> >> Now I want to add "Query Timeout" functionality to it >> >> For that I need to get the PID for last ran mysql query and then using >> kill >> PID - I can kill the process on MySQL server >> >> Please explain how to do that in PHP Thanks! >> >> Similar to what described @ http://bytes.com/forum/thread156058.html > > Learn some php and convert the perl script? > > What area are you having issues with exactly? > > -- > Postgresql & php tutorials > http://www.designmagick.com/ > > -- Thanks & Regards, -Piyush Mo.: 091-9910904233 Mail: [EMAIL PROTECTED] Web: http://piyush.me/ -In a world without fences, limits, boundaries and walls, Who needs Windows and Gates? -- Thanks & Regards, -Piyush Mo.: 091-9910904233 Mail: [EMAIL PROTECTED] Web: http://piyush.me/ -In a world without fences, limits, boundaries and walls, Who needs Windows and Gates? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL Query Timeout program in PHP
Man- when the query is fired through the web interface- it rans on mysql server max_execution_time - wont' help evn I stop apache itself... The query runs on mysql server - so I have to kill the PID on server itself... Thanks Piyush On Thu, Oct 16, 2008 at 7:57 AM, Jack van Zanen <[EMAIL PROTECTED]> wrote: > Just put the time out in your PHP.INI file > > max_execution_time = 30 ; Maximum execution time of each script, in > seconds > > 2008/10/16 Piyush Kumar <[EMAIL PROTECTED]> >> >> I'm using http://myclient.polarlava.com/ as web query interface for mysql >> server >> >> Now I want to add "Query Timeout" functionality to it >> >> For that I need to get the PID for last ran mysql query and then using >> kill >> PID - I can kill the process on MySQL server >> >> Please explain how to do that in PHP Thanks! >> >> Similar to what described @ http://bytes.com/forum/thread156058.html >> >> >> -- >> Thanks & Regards, >> -Piyush >> Mo.: 091-9910904233 >> Mail: [EMAIL PROTECTED] >> Web: http://piyush.me/ >> >> -In a world without fences, limits, boundaries and walls, Who needs >> Windows and Gates? >> >> -- >> PHP Database Mailing List (http://www.php.net/) >> To unsubscribe, visit: http://www.php.net/unsub.php >> > > > > -- > J.A. van Zanen > -- Thanks & Regards, -Piyush Mo.: 091-9910904233 Mail: [EMAIL PROTECTED] Web: http://piyush.me/ -In a world without fences, limits, boundaries and walls, Who needs Windows and Gates? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL Query Timeout program in PHP
Just put the time out in your PHP.INI file max_execution_time = 30 ; Maximum execution time of each script, in seconds 2008/10/16 Piyush Kumar <[EMAIL PROTECTED]> > I'm using http://myclient.polarlava.com/ as web query interface for mysql > server > > Now I want to add "Query Timeout" functionality to it > > For that I need to get the PID for last ran mysql query and then using kill > PID - I can kill the process on MySQL server > > Please explain how to do that in PHP Thanks! > > Similar to what described @ http://bytes.com/forum/thread156058.html > > > -- > Thanks & Regards, > -Piyush > Mo.: 091-9910904233 > Mail: [EMAIL PROTECTED] > Web: http://piyush.me/ > > -In a world without fences, limits, boundaries and walls, Who needs > Windows and Gates? > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- J.A. van Zanen
Re: [PHP-DB] MySQL Query Timeout program in PHP
Piyush Kumar wrote: I'm using http://myclient.polarlava.com/ as web query interface for mysql server Now I want to add "Query Timeout" functionality to it For that I need to get the PID for last ran mysql query and then using kill PID - I can kill the process on MySQL server Please explain how to do that in PHP Thanks! Similar to what described @ http://bytes.com/forum/thread156058.html Learn some php and convert the perl script? What area are you having issues with exactly? -- 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] MySQL Query Timeout program in PHP
I'm using http://myclient.polarlava.com/ as web query interface for mysql server Now I want to add "Query Timeout" functionality to it For that I need to get the PID for last ran mysql query and then using kill PID - I can kill the process on MySQL server Please explain how to do that in PHP Thanks! Similar to what described @ http://bytes.com/forum/thread156058.html -- Thanks & Regards, -Piyush Mo.: 091-9910904233 Mail: [EMAIL PROTECTED] Web: http://piyush.me/ -In a world without fences, limits, boundaries and walls, Who needs Windows and Gates? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MySQL Query on the Fly
That's one of the uses of Ajax. http://www.xajaxproject.org/ http://wiki.xajaxproject.org/Tutorials:Learn_xajax_in_10_Minutes You don't "have to use an iframe". Regards, Dwight > -Original Message- > From: Todd A. Dorschner [mailto:[EMAIL PROTECTED] > Sent: Friday, May 11, 2007 8:31 AM > To: php-db@lists.php.net > Subject: [PHP-DB] MySQL Query on the Fly > > Good Morning, > > > > I've been searching and toying with this solution for some time but > can't find that right answer. Looking for solutions/suggestions to the > following: > > > > I created a program that will allow people to track sales and depending > on what they've sold, they will either get a set bonus, or a bonus based > on a percentage. I have no problem creating a drop down box for them to > select from, and I could probably create a select button that would do > the action, but I'd rather have it in one step. So what I'm trying to > do is somehow when they select from the drop down (via a JavaScript > onchange or something) I'd like it to pull data from the MySQL database. > I'd like it to pull the amount and then each or per based on the ID of > what they've selected from the drop down. If it was always based on > each sale or based on a percentage, that would be easy as I could code > that into the drop box, but I'd like to keep it based on the ID so that > it can pull out of the database the name, and then when changed pull the > info. Any ideas? Thanks in advance for the help. If you have any > questions or it seems like I left something out, please let me know. > > > > > > Thank you for your time, > > > Todd Dorschner -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL Query on the Fly
Good Morning, I've been searching and toying with this solution for some time but can't find that right answer. Looking for solutions/suggestions to the following: I created a program that will allow people to track sales and depending on what they've sold, they will either get a set bonus, or a bonus based on a percentage. I have no problem creating a drop down box for them to select from, and I could probably create a select button that would do the action, but I'd rather have it in one step. So what I'm trying to do is somehow when they select from the drop down (via a JavaScript onchange or something) I'd like it to pull data from the MySQL database. I'd like it to pull the amount and then each or per based on the ID of what they've selected from the drop down. If it was always based on each sale or based on a percentage, that would be easy as I could code that into the drop box, but I'd like to keep it based on the ID so that it can pull out of the database the name, and then when changed pull the info. Any ideas? Thanks in advance for the help. If you have any questions or it seems like I left something out, please let me know. Thank you for your time, Todd Dorschner
[PHP-DB] MySQL Query Help
Hello, I'm unsure how to write the follow query, Please can someone assist. I am writing in short hand the basis of the two queries I would like to combine. The object is to select all the products that have not meet the min sale requirements so I can send myself a reminder email. The first table products contains the product information and how ofter the stores must purchase and at what period ie, Weekly or Monthly by product. First Query gives me all the products that must check to see if they have meet the min sales. Select store_id,product_id, min_level, reoirder_period from products db where monthly_order_required > 0 Second Query is then run to test to see if they have meet the min sales. Php: If reorder period = weekly then backdate = 7 days elseif period = monthly then backdate = 30 Select sum(sale_product_qty) as sale_period_total from sales db where store = store_id and product = product_id and date <= now and date > backdate and sale_period_total > min_level Result: sum of sales in the last week or month period for that product from that store. If no result then sale level ok. If result then the difference from min_level is what is require for the store to make asap. Php: if sale_period_total < min_level then email low order email. Any assistance to combine to the queries instead of hundreds of individual would be grateful. Regards Mark Dyer NZ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MySQL Query
SELECT c.eventid, e.ID FROM col_links c left outer join calendar_v2 e ON c.eventid = e.ID where e.id is null LIMIT 0, 999; From: "Keith Spiller" <[EMAIL PROTECTED]> To: "[PHP-DB]" Subject: [PHP-DB] MySQL Query Date: Sat, 19 Nov 2005 19:29:25 -0700 Hi, I'm tring to run this query to find orphan entries. SELECT c.eventid, e.ID FROM col_links c left outer join calendar_v2 e ON c.eventid = e.ID LIMIT 0, 999; Then I have to scan the list to find entries where e.ID equals NULL. I was wondering if anyone know how to expand this query with a WHERE clause that would find just the orphan instances. Thanks, Keith -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL Query
Hi, I'm tring to run this query to find orphan entries. SELECT c.eventid, e.ID FROM col_links c left outer join calendar_v2 e ON c.eventid = e.ID LIMIT 0, 999; Then I have to scan the list to find entries where e.ID equals NULL. I was wondering if anyone know how to expand this query with a WHERE clause that would find just the orphan instances. Thanks, Keith
RE: [PHP-DB] MySQL query result with some errors!
The code looks correct so my guess is that you are parsing it inorrectly in actionscript... Bastien From: Juan Stiller <[EMAIL PROTECTED]> To: php-db@lists.php.net Subject: [PHP-DB] MySQL query result with some errors! Date: Tue, 11 Oct 2005 18:14:42 -0300 (ART) Hi, Im trying to get a query result from mysql database to show it on a flash animation, so far i have this code that its supposed to connect to mysql server , make the query and get the results, store them on variables so that flash can read them. Basically i have these main fileds like : lastname, firstname and so on... a guy here told me correctly that i should in case to have more than one entry on the database, use the output like lastname1, lastname2, etc. I gues this code is doing that, but when i want to show that info on flash i get eg: lastname: smithn=0 lastname: jacksonn=1 when it should be lastname: smith lastname: jackson ... ... So i guess im doing something wrong, can someone point me in the right direcction?? Here is the php code: No se pudo conectar " . "al servidor MySQL." ); exit(); } if (! @mysql_select_db("llamadas") ) { echo( "No se puede encontrar " . "la base de datos clientes!" ); exit(); } $qr = mysql_query("select * from datos"); $nrows = mysql_num_rows($qr); $rString = "n=".$nrows; for ($i=0; $i < $nrows; $i++) { $row = mysql_fetch_array($qr); $rstring .= "&Id".$i."=".$row['id']; $rString .= "&Dia".$i."=".$row['dia']."&Mes".$i."=".$row['mes']."&Ano".$i."=".$row['ano']; $rString .= "&Hora".$i."=".$row['hora']."&Minutos".$i."=".$row['minutos']; $rString .= "&Receptor".$i."=".$row['receptor']. $rString .= "&Destinatario".$i."=".$row['destinatario']. $rString .= "&Apellido".$i."=".$row['apellido']. $rstring .= "&Nombre".$i."=".$row['nombre']; $rstring .= "&Telefono".$i."=".$row['telefono']; $rstring .= "&Asunto".$i."=".$row['asunto']; } echo $rString; mysql_free_result($qr); ?> Thanks. Juan ___ 1GB gratis, Antivirus y Antispam Correo Yahoo!, el mejor correo web del mundo http://correo.yahoo.com.ar -- 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] MySQL query result with some errors!
Hi, Im trying to get a query result from mysql database to show it on a flash animation, so far i have this code that its supposed to connect to mysql server , make the query and get the results, store them on variables so that flash can read them. Basically i have these main fileds like : lastname, firstname and so on... a guy here told me correctly that i should in case to have more than one entry on the database, use the output like lastname1, lastname2, etc. I gues this code is doing that, but when i want to show that info on flash i get eg: lastname: smithn=0 lastname: jacksonn=1 when it should be lastname: smith lastname: jackson ... ... So i guess im doing something wrong, can someone point me in the right direcction?? Here is the php code: No se pudo conectar " . "al servidor MySQL." ); exit(); } if (! @mysql_select_db("llamadas") ) { echo( "No se puede encontrar " . "la base de datos clientes!" ); exit(); } $qr = mysql_query("select * from datos"); $nrows = mysql_num_rows($qr); $rString = "n=".$nrows; for ($i=0; $i < $nrows; $i++) { $row = mysql_fetch_array($qr); $rstring .= "&Id".$i."=".$row['id']; $rString .= "&Dia".$i."=".$row['dia']."&Mes".$i."=".$row['mes']."&Ano".$i."=".$row['ano']; $rString .= "&Hora".$i."=".$row['hora']."&Minutos".$i."=".$row['minutos']; $rString .= "&Receptor".$i."=".$row['receptor']. $rString .= "&Destinatario".$i."=".$row['destinatario']. $rString .= "&Apellido".$i."=".$row['apellido']. $rstring .= "&Nombre".$i."=".$row['nombre']; $rstring .= "&Telefono".$i."=".$row['telefono']; $rstring .= "&Asunto".$i."=".$row['asunto']; } echo $rString; mysql_free_result($qr); ?> Thanks. Juan ___ 1GB gratis, Antivirus y Antispam Correo Yahoo!, el mejor correo web del mundo http://correo.yahoo.com.ar -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL query result with some errors!
Hi, Im trying to get a query result from mysql database to show it on a flash animation, so far i have this code that its supposed to connect to mysql server , make the query and get the results, store them on variables so that flash can read them. Basically i have these main fileds like : lastname, firstname and so on... a guy here told me correctly that i should in case to have more than one entry on the database, use the output like lastname1, lastname2, etc. I gues this code is doing that, but when i want to show that info on flash i get eg: lastname: smithn=0 lastname: jacksonn=1 when it should be lastname: smith lastname: jackson ... ... So i guess im doing something wrong, can someone point me in the right direcction?? Here is the php code: No se pudo conectar " . "al servidor MySQL." ); exit(); } if (! @mysql_select_db("llamadas") ) { echo( "No se puede encontrar " . "la base de datos clientes!" ); exit(); } $qr = mysql_query("select * from datos"); $nrows = mysql_num_rows($qr); $rString = "n=".$nrows; for ($i=0; $i < $nrows; $i++) { $row = mysql_fetch_array($qr); $rstring .= "&Id".$i."=".$row['id']; $rString .= "&Dia".$i."=".$row['dia']."&Mes".$i."=".$row['mes']."&Ano".$i."=".$row['ano']; $rString .= "&Hora".$i."=".$row['hora']."&Minutos".$i."=".$row['minutos']; $rString .= "&Receptor".$i."=".$row['receptor']. $rString .= "&Destinatario".$i."=".$row['destinatario']. $rString .= "&Apellido".$i."=".$row['apellido']. $rstring .= "&Nombre".$i."=".$row['nombre']; $rstring .= "&Telefono".$i."=".$row['telefono']; $rstring .= "&Asunto".$i."=".$row['asunto']; } echo $rString; mysql_free_result($qr); ?> Thanks. Juan ___ 1GB gratis, Antivirus y Antispam Correo Yahoo!, el mejor correo web del mundo http://correo.yahoo.com.ar -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL query result with some errors!
Hi, Im trying to get a query result from mysql database to show it on a flash animation, so far i have this code that its supposed to connect to mysql server , make the query and get the results, store them on variables so that flash can read them. Basically i have these main fileds like : lastname, firstname and so on... a guy here told me correctly that i should in case to have more than one entry on the database, use the output like lastname1, lastname2, etc. I gues this code is doing that, but when i want to show that info on flash i get eg: lastname: smithn=0 lastname: jacksonn=1 when it should be lastname: smith lastname: jackson ... ... So i guess im doing something wrong, can someone point me in the right direcction?? Here is the php code: No se pudo conectar " . "al servidor MySQL." ); exit(); } if (! @mysql_select_db("llamadas") ) { echo( "No se puede encontrar " . "la base de datos clientes!" ); exit(); } $qr = mysql_query("select * from datos"); $nrows = mysql_num_rows($qr); $rString = "n=".$nrows; for ($i=0; $i < $nrows; $i++) { $row = mysql_fetch_array($qr); $rstring .= "&Id".$i."=".$row['id']; $rString .= "&Dia".$i."=".$row['dia']."&Mes".$i."=".$row['mes']."&Ano".$i."=".$row['ano']; $rString .= "&Hora".$i."=".$row['hora']."&Minutos".$i."=".$row['minutos']; $rString .= "&Receptor".$i."=".$row['receptor']. $rString .= "&Destinatario".$i."=".$row['destinatario']. $rString .= "&Apellido".$i."=".$row['apellido']. $rstring .= "&Nombre".$i."=".$row['nombre']; $rstring .= "&Telefono".$i."=".$row['telefono']; $rstring .= "&Asunto".$i."=".$row['asunto']; } echo $rString; mysql_free_result($qr); ?> Thanks. Juan ___ 1GB gratis, Antivirus y Antispam Correo Yahoo!, el mejor correo web del mundo http://correo.yahoo.com.ar -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL query + PHP : Warning: implode() [function.implode]: Bad arguments : What's wrong here?
I have 2 tables employees & transactions. 'employees' carry info about the employees and transactions carry there payslip info for each period. I am trying to display a list of employees that don't have transactions in the current ($pp_id) period, thus doing an array of the $emp_ids, having transactions in the current period in order to exclude them in the next query. I am using MySQL 4.1.11 Any help will be appreciated. $pp_id = $_GET['pp_id']; $t1 = "employees"; $t2 = "transactions"; $query = mysql_query("SELECT `emp_id` FROM $t2 WHERE `pp_id` = '$pp_id'"); while ($row = @mysql_fetch_array($query)){ $emp_ids=$row['emp_id']; } line 83:$select_employee_query = mysql_query(" SELECT $t1.* FROM $t1 WHERE emp_id IN (" . implode(', ', $emp_ids) . ") "); while ($row = @mysql_fetch_array($select_employee_query)) { $var3=$row['emp_num']; $var4=$row['emp_title']; $var5=$row['emp_initials']; $var6=$row['emp_surname']; echo "$var3 $var4 $var5 $var6"; } Warning: implode() [function.implode]: Bad arguments. in ../add_payslip1.php on line 83 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] mysql query plan
is there a join in the sql? Joins can be tricky since the order of the joins can determine the number of records selected from each table. If the joins result in a larger table joining with a smaller table, it could result in more than 30% of the rows being selected and the optimizer will ignore the index. Try moving the order of the records around. You may even want to split the query into pieces and let the application logic handle the resultsets. I had to do this with a long running query that produced 100 records for export (4+ minutes in a web environment). By splitting the 5 table join into 3 queries and application based arrays, I reduced the run time to about 10 seconds with 50x the output. Bastien From: Eddie Peloke <[EMAIL PROTECTED]> Reply-To: Eddie Peloke <[EMAIL PROTECTED]> To: php-db@lists.php.net Subject: [PHP-DB] mysql query plan Date: Wed, 13 Apr 2005 15:42:41 -0400 Hello, We are working to optimize a few queries. When we run the query with Explain, it appears that the query optimizer is not using one of the tables index and doing a full table scan while it appears to use other tables' indices properly. We then remove the table with the full scan, run again with Explain and now the optimizer seems to ignore the index of one of the other tables when it correctly used that table's index before in the previous run. Anyone have some good insight into how the optimizer picks it's query plan? It seems to pick one table and ignore the index for some reason. Thanks! -- 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] mysql query plan
Hello, We are working to optimize a few queries. When we run the query with Explain, it appears that the query optimizer is not using one of the tables index and doing a full table scan while it appears to use other tables' indices properly. We then remove the table with the full scan, run again with Explain and now the optimizer seems to ignore the index of one of the other tables when it correctly used that table's index before in the previous run. Anyone have some good insight into how the optimizer picks it's query plan? It seems to pick one table and ignore the index for some reason. Thanks! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MySQL query problems...
OK. I think I have resolved this issue. The problem I am having now is that I have a simple table that is a list of systems to exclude from the display. I am having trouble figuring out how to structure my query to actually exclude the systems in this table. Here is what I tried, and it gives me 13 instances of every entry, 13 is the number of hostnames in the exclusion table. $query = "SELECT AllMid_Data.CPU_Hostname FROM AllMid_Data, exclusion WHERE AllMid_Data.CPU_IN_SVC = \"Y\" AND AllMid_Data.CPU_DNS = \"sbcld.sbc.com\" AND AllMid_Data.CPU_Hostname != exclusion.hostname ORDER BY CPU_Hostname ASC"; $results = mysql_query($query, $Prod) or die(mysql_error()); $system = mysql_fetch_assoc($results); Additionally, what I am actually working towards is that if a system shows up in either of the other queries it gets excluded from this one. I have not tried anything to make that happen yet. I will tackle that challenge once I can resolve this part. Thanks again. Scott Nipp Phone: (214) 858-1289 E-mail: [EMAIL PROTECTED] Web: http:\\ldsa.sbcld.sbc.com -Original Message- From: J. Connolly [mailto:[EMAIL PROTECTED] Sent: Thursday, March 24, 2005 7:36 AM Cc: php-db@lists.php.net Subject: Re: [PHP-DB] MySQL query problems... yeah...errora are important. I do not escape my quotation marks in a query. I use single quotes within my insert and select statements. If it says "No valis MySQL resource then you are returning 0 records which may be caused by escaping the quotations. I follow this pattern $sql ="INSERT INTO VALUES ('some value','another value','etc')"; NIPP, SCOTT V (SBCSI) wrote: > I am getting errors for the following queries. This query seems >to work fine in phpMyAdmin. > >$query2 = "SELECT AllMid_Data.CPU_Hostname FROM AllMid_Data, accounts > WHERE AllMid_Data.CPU_IN_SVC = \"Y\" > AND AllMid_Data.CPU_DNS = \"sbcld.sbc.com\" > AND accounts.sbcuid = $sbcuid > AND accounts.system = AllMid_Data.CPU_Hostname > AND accounts.ctime IS NULL > ORDER BY CPU_Hostname ASC"; >$results2 = mysql_query($query2, $Prod) or die(mysql_error()); >$system2 = mysql_fetch_assoc($results2); >$query3 = "SELECT AllMid_Data.CPU_Hostname FROM AllMid_Data, acct_db > WHERE AllMid_Data.CPU_IN_SVC = \"Y\" > AND AllMid_Data.CPU_DNS = \"sbcld.sbc.com\" > AND acct_db.key1 LIKE \"$sbcuid%\" > AND acct_db.key1 LIKE \"%\".AllMid_Data.CPU_Hostname > ORDER BY CPU_Hostname ASC"; >$results3 = mysql_query($query3, $Prod) or die(mysql_error()); >$system3 = mysql_fetch_assoc($results3); > > I am assuming that I have an issue with query3. Thanks in >advance. >Scott Nipp >Phone: (214) 858-1289 >E-mail: [EMAIL PROTECTED] >Web: http:\\ldsa.sbcld.sbc.com > > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL query problems...
Not quite correct, "No valis MySQL resource" means the query and/or link failed in connection in some way. A correct and proper query MAY easily return 0 rows and that is correct and will give you a valid resource (handle) and simply no data. Bastien From: "J. Connolly" <[EMAIL PROTECTED]> CC: php-db@lists.php.net Subject: Re: [PHP-DB] MySQL query problems... Date: Thu, 24 Mar 2005 08:35:56 -0500 yeah...errora are important. I do not escape my quotation marks in a query. I use single quotes within my insert and select statements. If it says "No valis MySQL resource then you are returning 0 records which may be caused by escaping the quotations. I follow this pattern $sql ="INSERT INTO VALUES ('some value','another value','etc')"; NIPP, SCOTT V (SBCSI) wrote: I am getting errors for the following queries. This query seems to work fine in phpMyAdmin. $query2 = "SELECT AllMid_Data.CPU_Hostname FROM AllMid_Data, accounts WHERE AllMid_Data.CPU_IN_SVC = \"Y\" AND AllMid_Data.CPU_DNS = \"sbcld.sbc.com\" AND accounts.sbcuid = $sbcuid AND accounts.system = AllMid_Data.CPU_Hostname AND accounts.ctime IS NULL ORDER BY CPU_Hostname ASC"; $results2 = mysql_query($query2, $Prod) or die(mysql_error()); $system2 = mysql_fetch_assoc($results2); $query3 = "SELECT AllMid_Data.CPU_Hostname FROM AllMid_Data, acct_db WHERE AllMid_Data.CPU_IN_SVC = \"Y\" AND AllMid_Data.CPU_DNS = \"sbcld.sbc.com\" AND acct_db.key1 LIKE \"$sbcuid%\" AND acct_db.key1 LIKE \"%\".AllMid_Data.CPU_Hostname ORDER BY CPU_Hostname ASC"; $results3 = mysql_query($query3, $Prod) or die(mysql_error()); $system3 = mysql_fetch_assoc($results3); I am assuming that I have an issue with query3. Thanks in advance. Scott Nipp Phone: (214) 858-1289 E-mail: [EMAIL PROTECTED] Web: http:\\ldsa.sbcld.sbc.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL query problems...
yeah...errora are important. I do not escape my quotation marks in a query. I use single quotes within my insert and select statements. If it says "No valis MySQL resource then you are returning 0 records which may be caused by escaping the quotations. I follow this pattern $sql ="INSERT INTO VALUES ('some value','another value','etc')"; NIPP, SCOTT V (SBCSI) wrote: I am getting errors for the following queries. This query seems to work fine in phpMyAdmin. $query2 = "SELECT AllMid_Data.CPU_Hostname FROM AllMid_Data, accounts WHERE AllMid_Data.CPU_IN_SVC = \"Y\" AND AllMid_Data.CPU_DNS = \"sbcld.sbc.com\" AND accounts.sbcuid = $sbcuid AND accounts.system = AllMid_Data.CPU_Hostname AND accounts.ctime IS NULL ORDER BY CPU_Hostname ASC"; $results2 = mysql_query($query2, $Prod) or die(mysql_error()); $system2 = mysql_fetch_assoc($results2); $query3 = "SELECT AllMid_Data.CPU_Hostname FROM AllMid_Data, acct_db WHERE AllMid_Data.CPU_IN_SVC = \"Y\" AND AllMid_Data.CPU_DNS = \"sbcld.sbc.com\" AND acct_db.key1 LIKE \"$sbcuid%\" AND acct_db.key1 LIKE \"%\".AllMid_Data.CPU_Hostname ORDER BY CPU_Hostname ASC"; $results3 = mysql_query($query3, $Prod) or die(mysql_error()); $system3 = mysql_fetch_assoc($results3); I am assuming that I have an issue with query3. Thanks in advance. Scott Nipp Phone: (214) 858-1289 E-mail: [EMAIL PROTECTED] Web: http:\\ldsa.sbcld.sbc.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MySQL query problems...
um, the errors might be helpful bastien From: "NIPP, SCOTT V (SBCSI)" <[EMAIL PROTECTED]> To: Subject: [PHP-DB] MySQL query problems... Date: Wed, 23 Mar 2005 17:18:44 -0600 I am getting errors for the following queries. This query seems to work fine in phpMyAdmin. $query2 = "SELECT AllMid_Data.CPU_Hostname FROM AllMid_Data, accounts WHERE AllMid_Data.CPU_IN_SVC = \"Y\" AND AllMid_Data.CPU_DNS = \"sbcld.sbc.com\" AND accounts.sbcuid = $sbcuid AND accounts.system = AllMid_Data.CPU_Hostname AND accounts.ctime IS NULL ORDER BY CPU_Hostname ASC"; $results2 = mysql_query($query2, $Prod) or die(mysql_error()); $system2 = mysql_fetch_assoc($results2); $query3 = "SELECT AllMid_Data.CPU_Hostname FROM AllMid_Data, acct_db WHERE AllMid_Data.CPU_IN_SVC = \"Y\" AND AllMid_Data.CPU_DNS = \"sbcld.sbc.com\" AND acct_db.key1 LIKE \"$sbcuid%\" AND acct_db.key1 LIKE \"%\".AllMid_Data.CPU_Hostname ORDER BY CPU_Hostname ASC"; $results3 = mysql_query($query3, $Prod) or die(mysql_error()); $system3 = mysql_fetch_assoc($results3); I am assuming that I have an issue with query3. Thanks in advance. Scott Nipp Phone: (214) 858-1289 E-mail: [EMAIL PROTECTED] Web: http:\\ldsa.sbcld.sbc.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] MySQL query problems...
I am getting errors for the following queries. This query seems to work fine in phpMyAdmin. $query2 = "SELECT AllMid_Data.CPU_Hostname FROM AllMid_Data, accounts WHERE AllMid_Data.CPU_IN_SVC = \"Y\" AND AllMid_Data.CPU_DNS = \"sbcld.sbc.com\" AND accounts.sbcuid = $sbcuid AND accounts.system = AllMid_Data.CPU_Hostname AND accounts.ctime IS NULL ORDER BY CPU_Hostname ASC"; $results2 = mysql_query($query2, $Prod) or die(mysql_error()); $system2 = mysql_fetch_assoc($results2); $query3 = "SELECT AllMid_Data.CPU_Hostname FROM AllMid_Data, acct_db WHERE AllMid_Data.CPU_IN_SVC = \"Y\" AND AllMid_Data.CPU_DNS = \"sbcld.sbc.com\" AND acct_db.key1 LIKE \"$sbcuid%\" AND acct_db.key1 LIKE \"%\".AllMid_Data.CPU_Hostname ORDER BY CPU_Hostname ASC"; $results3 = mysql_query($query3, $Prod) or die(mysql_error()); $system3 = mysql_fetch_assoc($results3); I am assuming that I have an issue with query3. Thanks in advance. Scott Nipp Phone: (214) 858-1289 E-mail: [EMAIL PROTECTED] Web: http:\\ldsa.sbcld.sbc.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL - Query within a query
I am looking to do the following w/ MySQL and PHP. Have 1 query that gets results, then use those results in another query: $l = 2; $result1 = ("SELECT * FROM Drivers WHERE League = $l"); $result2 = mysql_query("SELECT DriverID, Driver, CarNbr FROM Drivers LEFT JOIN $LeagueList ON Drivers.DriverID = $Result1.DriverID WHERE $Result1.DriverID) Is Null ORDER BY Drivers.DriverID"); If you're using MySQL 4.1, which supports subqueries, you might be able to write this all up as one nice query (using a sub-select). Larry -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MySQL - Query within a query
Unlike ASP, $result1 is a handle to the dataset, not the data set itself. You need to access the data first and then incorporate that into your query like this: $l = 2; $result1 = ("SELECT * FROM Drivers WHERE League = $l"); $row = mysql_fetch_array($result1); $driverID = $row['DriverID']; $result2 = mysql_query("SELECT DriverID, Driver, CarNbr FROM Drivers LEFT JOIN $LeagueList ON Drivers.DriverID = $driverID WHERE $driverID) Is Null ORDER BY Drivers.DriverID"); hth Bastien From: "Pete Ruby" <[EMAIL PROTECTED]> Reply-To: "Pete Ruby" <[EMAIL PROTECTED]> To: php-db@lists.php.net Subject: [PHP-DB] MySQL - Query within a query Date: Wed, 16 Mar 2005 00:03:19 -0500 I am looking to do the following w/ MySQL and PHP. Have 1 query that gets results, then use those results in another query: $l = 2; $result1 = ("SELECT * FROM Drivers WHERE League = $l"); $result2 = mysql_query("SELECT DriverID, Driver, CarNbr FROM Drivers LEFT JOIN $LeagueList ON Drivers.DriverID = $Result1.DriverID WHERE $Result1.DriverID) Is Null ORDER BY Drivers.DriverID"); I can do it w/ Access and MSSQL, but if I try in MySQL, i get an error, both in DBManager (MySQL interface), and in PHP. Any help is appreciated. Thanx! Pete -- 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] MySQL - Query within a query
I am looking to do the following w/ MySQL and PHP. Have 1 query that gets results, then use those results in another query: $l = 2; $result1 = ("SELECT * FROM Drivers WHERE League = $l"); $result2 = mysql_query("SELECT DriverID, Driver, CarNbr FROM Drivers LEFT JOIN $LeagueList ON Drivers.DriverID = $Result1.DriverID WHERE $Result1.DriverID) Is Null ORDER BY Drivers.DriverID"); I can do it w/ Access and MSSQL, but if I try in MySQL, i get an error, both in DBManager (MySQL interface), and in PHP. Any help is appreciated. Thanx! Pete -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] mysql query
On Fri, 13 Aug 2004 19:39:59 +0200, Torsten Roehr <[EMAIL PROTECTED]> wrote: > "Justin Patrin" <[EMAIL PROTECTED]> wrote in message > news:[EMAIL PROTECTED] > > On Fri, 13 Aug 2004 15:20:36 +0200, Quentin Cotillard > > <[EMAIL PROTECTED]> wrote: > > > Consider a table similar to the one below. > > > What I want to do is to get ONE random record from categories(cat) A > > > and 5 random record from category B > > > > > > | ID | computer | name | cat |... > > >1dell 834A > > >2ibm526A > > >3apple 134B > > >4sony 333A > > >5dell 834B > > >6ibm556A > > >7apple 534B > > >8sony 233A > > >9dell 874A > > > > > > > > > How could I construct my query to the mysql? > > > > > > > This is an SQL question, not a PHP question. > > > > order by rand limit 5 > > Hi Justin, > > this won't work because he needs to make sure to get 1 from category A *AND* > 5 from category B with one query. I had a similar problem some weeks ago and > even though a lot of people from the list were trying to help we didn't find > a solution. In the end I had to do seperate queries. > I actually assumed that. This *can* be done in a query, but it requires sub-queriesunions. Something like this: SELECT * FROM computer WHERE cat = 'A' ORDER BY rand() LIMIT 1 UNION SELECT * FROM computer WHERE cat = 'B' ORDER BY rand() LIMIT 5 -- 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] mysql query
"Justin Patrin" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > On Fri, 13 Aug 2004 15:20:36 +0200, Quentin Cotillard > <[EMAIL PROTECTED]> wrote: > > Consider a table similar to the one below. > > What I want to do is to get ONE random record from categories(cat) A > > and 5 random record from category B > > > > | ID | computer | name | cat |... > >1dell 834A > >2ibm526A > >3apple 134B > >4sony 333A > >5dell 834B > >6ibm556A > >7apple 534B > >8sony 233A > >9dell 874A > > > > > > How could I construct my query to the mysql? > > > > This is an SQL question, not a PHP question. > > order by rand limit 5 Hi Justin, this won't work because he needs to make sure to get 1 from category A *AND* 5 from category B with one query. I had a similar problem some weeks ago and even though a lot of people from the list were trying to help we didn't find a solution. In the end I had to do seperate queries. Regards, Torsten -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] mysql query
On Fri, 13 Aug 2004 15:20:36 +0200, Quentin Cotillard <[EMAIL PROTECTED]> wrote: > Consider a table similar to the one below. > What I want to do is to get ONE random record from categories(cat) A > and 5 random record from category B > > | ID | computer | name | cat |... >1dell 834A >2ibm526A >3apple 134B >4sony 333A >5dell 834B >6ibm556A >7apple 534B >8sony 233A >9dell 874A > > > How could I construct my query to the mysql? > This is an SQL question, not a PHP question. order by rand limit 5 -- 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] mysql query
Consider a table similar to the one below. What I want to do is to get ONE random record from categories(cat) A and 5 random record from category B | ID | computer | name | cat |... 1 dell 834A 2 ibm526A 3 apple 134B 4 sony 333A 5 dell 834B 6 ibm556A 7 apple 534B 8 sony 233A 9 dell 874A ... How could I construct my query to the mysql? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL query, using DISTINCT...
On Tuesday 06 July 2004 17:58, [EMAIL PROTECTED] wrote: > I have a system that tracks downloads, capturing loadsa info, but of > interest here, is email and filename. > Simply put, I wanna show all results where file name AND email are unique. > (so if email '[EMAIL PROTECTED]' has filename 'word.doc' 5 times in a table, > I want to only see it once.) > > What am I doing wrong...? Well what did you expect your code to do when you run it, and what is it actually doing? Please spend a little time describing your problem clearly and succintly when posting. In the absence of more information, my guess is that your biggest problem is that you're not doing sufficient error checking in your code. AFAICS ... > SELECT DISTINCT(file_name, email) FROM `completed_downloads` WHERE `bu` = > 'reech' AND date BETWEEN '2004-06-01' AND '2004-06-30' would give a mysql error. The correct syntax is: SELECT DISTINCT col1, col2, etc FROM table ... Please follow the examples in the manual and incorporate error checking when using mysql. -- Jason Wong -> Gremlins Associates -> www.gremlins.biz Open Source Software Systems Integrators * Web Design & Hosting * Internet & Intranet Applications Development * -- Search the list archives before you post http://marc.theaimsgroup.com/?l=php-db -- /* Nondeterminism means never having to say you are wrong. */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL query, using DISTINCT...
I have a system that tracks downloads, capturing loadsa info, but of interest here, is email and filename. Simply put, I wanna show all results where file name AND email are unique. (so if email '[EMAIL PROTECTED]' has filename 'word.doc' 5 times in a table, I want to only see it once.) What am I doing wrong...? SELECT DISTINCT(file_name, email) FROM `completed_downloads` WHERE `bu` = 'reech' AND date BETWEEN '2004-06-01' AND '2004-06-30' Tris... * The information contained in this e-mail message is intended only for the personal and confidential use of the recipient(s) named above. If the reader of this message is not the intended recipient or an agent responsible for delivering it to the intended recipient, you are hereby notified that you have received this document in error and that any review, dissemination, distribution, or copying of this message is strictly prohibited. If you have received this communication in error, please notify us immediately by e-mail, and delete the original message. *** -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MySql query
One reason that my queries will look like what you described is that later I append more conditions. The where 1 allows me to tack them all on with AND or OR instead of having to put the where on later and decide things like if I need to put AND or OR or WHERE or whatever... The WHERE 1 keeps it simple <>< Ryan -Original Message- From: Adam Voigt [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 17, 2004 10:15 AM To: Matt Matijevich Cc: [EMAIL PROTECTED] Subject: Re: [PHP-DB] MySql query Yes, where evaluates to true. On Wed, 2004-03-17 at 12:03, Matt Matijevich wrote: > This is probably a question for for a mysql list but I figured someone > out here knows the answer. > > I am working with an existing mysql application and there is a bunch > of queries that are formatted something like this: > > SELECT * FROM sometable WHERE 1 > > does "WHERE 1" just return every row? > > thanks in advance. -- Adam Voigt [EMAIL PROTECTED] -- 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] MySql query
Yes, where evaluates to true. On Wed, 2004-03-17 at 12:03, Matt Matijevich wrote: > This is probably a question for for a mysql list but I figured someone > out here knows the answer. > > I am working with an existing mysql application and there is a bunch of > queries that are formatted something like this: > > SELECT * FROM sometable WHERE 1 > > does "WHERE 1" just return every row? > > thanks in advance. -- Adam Voigt [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySql query
SELECT * FROM sometable Just that :) (do I hear you slapping your forehead? :) Bruno Ferreira. Thanks for the answers guys. My problem was I am a mysql novice and there was a bunch of existing queries that use the "WHERE 1" syntax. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySql query
Matt Matijevich wrote: This is probably a question for for a mysql list but I figured someone out here knows the answer. [snip] SELECT * FROM sometable WHERE 1 SELECT * FROM sometable Just that :) (do I hear you slapping your forehead? :) Bruno Ferreira. --- [This E-mail scanned for viruses by Declude Virus] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySql query
This is probably a question for for a mysql list but I figured someone out here knows the answer. I am working with an existing mysql application and there is a bunch of queries that are formatted something like this: SELECT * FROM sometable WHERE 1 does "WHERE 1" just return every row? thanks in advance. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL query... count?
Perfect... I finally used.. SELECT DISTINCT(bu), COUNT(bu) FROM $table_name WHERE date BETWEEN '$date1' AND '$date2' group by bu (I added the Distinct(bu) bit, to label the results correctly... god I love this list) Have a great day! Jason Wong <[EMAIL PROTECTED]> 01/03/2004 11:03 To [EMAIL PROTECTED] cc Subject Re: [PHP-DB] MySQL query... count? On Monday 01 March 2004 18:53, [EMAIL PROTECTED] wrote: > What I need to do for a new report, is say how many downloads were > recorded for each Business unit? > > I can select the Business units using Distinct, but hhow can I then count > them? > I've tried: > SELECT DISTINCT(bu)buname, COUNT(buname)bucount FROM $table_name > > But it doesn't work.. am I being dumb on this cold Monday morning? Don't use DISTINCT, use GROUP BY. -- Jason Wong -> Gremlins Associates -> www.gremlins.biz Open Source Software Systems Integrators * Web Design & Hosting * Internet & Intranet Applications Development * -- Search the list archives before you post http://marc.theaimsgroup.com/?l=php-db -- /* One thought driven home is better than three left on base. */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php * The information contained in this e-mail message is intended only for the personal and confidential use of the recipient(s) named above. If the reader of this message is not the intended recipient or an agent responsible for delivering it to the intended recipient, you are hereby notified that you have received this document in error and that any review, dissemination, distribution, or copying of this message is strictly prohibited. If you have received this communication in error, please notify us immediately by e-mail, and delete the original message. *** -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL query... count?
On Monday 01 March 2004 18:53, [EMAIL PROTECTED] wrote: > What I need to do for a new report, is say how many downloads were > recorded for each Business unit? > > I can select the Business units using Distinct, but hhow can I then count > them? > I've tried: > SELECT DISTINCT(bu)buname, COUNT(buname)bucount FROM $table_name > > But it doesn't work.. am I being dumb on this cold Monday morning? Don't use DISTINCT, use GROUP BY. -- Jason Wong -> Gremlins Associates -> www.gremlins.biz Open Source Software Systems Integrators * Web Design & Hosting * Internet & Intranet Applications Development * -- Search the list archives before you post http://marc.theaimsgroup.com/?l=php-db -- /* One thought driven home is better than three left on base. */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL query... count?
This problem pertains to a system that records all downloads from our site: I have a field in my DB, that states which business unit owns the download that was recorded... What I need to do for a new report, is say how many downloads were recorded for each Business unit? I can select the Business units using Distinct, but hhow can I then count them? I've tried: SELECT DISTINCT(bu)buname, COUNT(buname)bucount FROM $table_name But it doesn't work.. am I being dumb on this cold Monday morning? Tris... * The information contained in this e-mail message is intended only for the personal and confidential use of the recipient(s) named above. If the reader of this message is not the intended recipient or an agent responsible for delivering it to the intended recipient, you are hereby notified that you have received this document in error and that any review, dissemination, distribution, or copying of this message is strictly prohibited. If you have received this communication in error, please notify us immediately by e-mail, and delete the original message. *** -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL query question
i want to search the table called comments, and i want to count the number of times artid appears. then i want to group them all together, but then i want to order them by most appearances to fewest. so if artid 1 appeared 40 times and it was the most, i want that to be retrieved first. if artid 3 appeared 38 times and it was second most, i want that retrieved second... etc and so on. this code ive given is wrong, but i know that it has to be something like this or that im close. if any of you could help me id really appreciate it. if theres a shortcut with this code that would be great, otherwise i have to run my php around in a ton of different mysql queries trying to find out which has the most and have it ordered from highest to lowest. thank you for your help. ive tried mysql.com too and i cant find a thing there. plus the mailing lists for mysql, no one ever responds! thank you php list, you are my only hope. ;P -james $mostcomquery = "SELECT artid, COUNT(*) FROM comments GROUP BY artid ORDER BY (COUNT(*)) DESC LIMIT 5";
Re: [PHP-DB] MySQL Query not working via PHP
That solved the problem Steve. thanks, Sean "Steve Davies" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > remove the comma after cadet.photo, ie > > select cadet.relation2,cadet.photo FROM > > > HTH steve > > > Matthew Moldvan, Jr. wrote: > > >I would say try single quotes instead of double quotes ... let me know if > >that works ... > > > >Regards, > >Matt. > > > >- Original Message - > >From: "Sean Smitz" <[EMAIL PROTECTED]> > >To: <[EMAIL PROTECTED]> > >Sent: Friday, October 17, 2003 4:32 PM > >Subject: [PHP-DB] MySQL Query not working via PHP > > > > > > > > > >>I have a query that works in MySQL but when I try to execute it in PHP I > >>encounter the following error: > >> > >>//Generated by the script > >>Couldn't execute query! > >> > >>// Generated by MySQL > >>MySQL reports: You have an error in your SQL syntax. Check the manual that > >>corresponds to your MySQL server version for the right syntax to use near > >>'FROM cadet, cadet_phone WHERE cadet.ssn = cadet_phone.ssn AND ( > >> > >>//Query outputted by the script > >>Query was: SELECT cadet.rank, cadet.lname, cadet.fname, cadet.mname, > >>cadet.address1, cadet.address2, cadet.city, cadet.state, cadet.zipcode, > >>cadet.email, cadet.guardian1, cadet.relation1, cadet.guardian2, > >>cadet.realtion2, cadet.photo, FROM cadet, cadet_phone WHERE cadet.ssn = > >>cadet_phone.ssn AND (cadet.lname = "Smith" AND cadet.fname = "John" AND > >>cadet.mname = "Doe") > >> > >>Attached is the entire page. Any help is appreciated. > >> > >>Sean Smitz > >> > >> > >> > >>-- > >>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] MySQL Query not working via PHP
remove the comma after cadet.photo, ie select cadet.relation2,cadet.photo FROM HTH steve Matthew Moldvan, Jr. wrote: I would say try single quotes instead of double quotes ... let me know if that works ... Regards, Matt. - Original Message - From: "Sean Smitz" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, October 17, 2003 4:32 PM Subject: [PHP-DB] MySQL Query not working via PHP I have a query that works in MySQL but when I try to execute it in PHP I encounter the following error: //Generated by the script Couldn't execute query! // Generated by MySQL MySQL reports: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM cadet, cadet_phone WHERE cadet.ssn = cadet_phone.ssn AND ( //Query outputted by the script Query was: SELECT cadet.rank, cadet.lname, cadet.fname, cadet.mname, cadet.address1, cadet.address2, cadet.city, cadet.state, cadet.zipcode, cadet.email, cadet.guardian1, cadet.relation1, cadet.guardian2, cadet.realtion2, cadet.photo, FROM cadet, cadet_phone WHERE cadet.ssn = cadet_phone.ssn AND (cadet.lname = "Smith" AND cadet.fname = "John" AND cadet.mname = "Doe") Attached is the entire page. Any help is appreciated. Sean Smitz -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL Query not working via PHP
I would say try single quotes instead of double quotes ... let me know if that works ... Regards, Matt. - Original Message - From: "Sean Smitz" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, October 17, 2003 4:32 PM Subject: [PHP-DB] MySQL Query not working via PHP > I have a query that works in MySQL but when I try to execute it in PHP I > encounter the following error: > > //Generated by the script > Couldn't execute query! > > // Generated by MySQL > MySQL reports: You have an error in your SQL syntax. Check the manual that > corresponds to your MySQL server version for the right syntax to use near > 'FROM cadet, cadet_phone WHERE cadet.ssn = cadet_phone.ssn AND ( > > //Query outputted by the script > Query was: SELECT cadet.rank, cadet.lname, cadet.fname, cadet.mname, > cadet.address1, cadet.address2, cadet.city, cadet.state, cadet.zipcode, > cadet.email, cadet.guardian1, cadet.relation1, cadet.guardian2, > cadet.realtion2, cadet.photo, FROM cadet, cadet_phone WHERE cadet.ssn = > cadet_phone.ssn AND (cadet.lname = "Smith" AND cadet.fname = "John" AND > cadet.mname = "Doe") > > Attached is the entire page. Any help is appreciated. > > Sean Smitz > > > > -- > 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] MySQL Query not working via PHP
I have a query that works in MySQL but when I try to execute it in PHP I encounter the following error: //Generated by the script Couldn't execute query! // Generated by MySQL MySQL reports: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM cadet, cadet_phone WHERE cadet.ssn = cadet_phone.ssn AND ( //Query outputted by the script Query was: SELECT cadet.rank, cadet.lname, cadet.fname, cadet.mname, cadet.address1, cadet.address2, cadet.city, cadet.state, cadet.zipcode, cadet.email, cadet.guardian1, cadet.relation1, cadet.guardian2, cadet.realtion2, cadet.photo, FROM cadet, cadet_phone WHERE cadet.ssn = cadet_phone.ssn AND (cadet.lname = "Smith" AND cadet.fname = "John" AND cadet.mname = "Doe") Attached is the entire page. Any help is appreciated. Sean Smitz begin 666 test0.1.php M/#]P:' -"B @:68H([EMAIL PROTECTED]&),:6YK(#T@;7ES<6Q?8V]N;F5C="@B;&]C86QH M;W-T(BP@(FAT='!D(BP@(B(I*2D-"B @>PT*(" @('!R:6YT*")&86EL960@ M=&[EMAIL PROTECTED];F5C="!T;R!D871A8F%S92$\8G(^7&XB*3L-"B @("!P&ET*"D[#0H@('T-"B @#0H@("11=65R>2 ] M(")314Q%0U0@(B N#0H@(" @(F-A9&5T+G)A;FLL(&-A9&5T+FQN86UE+"!C M861E="YF;[EMAIL PROTECTED];6YA;64L("(@[EMAIL PROTECTED](" @(")C861E="YA9&1R M97-S,[EMAIL PROTECTED]3PO8CX\+W1D/EQN(BD[#0H@(" @<')I;G0H(CQT M9"!B9V-O;&]R/5PB(V-C8V-C8UPB/CQB/E-T871E/"]B/CPO=&0^7&XB*3L- M"B @("!P7!E M/"]B/CPO=&0^7&XB*3L-"B @("!P7-Q;%]F971C:%]AR1D8E)O=ULG9FYA;64G77T\+W1D/EQN(BD[#0H@ M(" @("!PR1D8E)O=ULG861DR1D8E)O=ULG9W5AR1D8E)O=ULGR1D8E)O=ULG<&AO;F5?;G5M)UU]/"]T9#Y<;B(I.PT*(" @(" @ M<')I;G0H(CQT9#Y[)&1B4F]W6R=T>7!E)UU]/"]T9#Y<;B(I.PT*(" @(" @ M<')I;G0H(CQT9#Y[)&1B4F]W6R=C;VYT86-T)UU]/"]T9#Y<;B(I.PT*(" @ M('!R:6YT*"(\+W1R/EQN(BD[#0H@('T-"@T*("!Phttp://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MYSQL Query problem...
Ian Bert Tusil wrote: is there something wrong with my SQL Query? I didnt get any results neither errors.. [snip] mysql_query($SQLQuery,$link); That's because you're not checking for errors... mysql_query($SQLQuery,$link) or die(mysql_error()); -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals – www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MYSQL Query problem...
is there something wrong with my SQL Query? I didnt get any results neither errors.. $link = mysql_connect('localhost', '') or die('Cannot connect to the database.'); mysql_select_db("profiles",$link); $SQLQuery = "INSERT INTO profyles (username, fname, mname, lname, address, country, gender, sexuality, status, bday, race, yahoo, msn, icq, email, webpage, hair, bodytype, eye, height, weight, facialhair, hobbies, interests, aboutme, profession, food, music, tvshows, authors, movies, rolemodel, place, visit, online, picture) VALUES ('$user', '$firstname', '$middlename', '$lastname ', '$address, '$country', '$gender', '$sexuality', '$status', '$birthday', '$race', '$yahoo', '$msn', '$icq', '$email'. '$webpage', 'haircolour', '$bodytype', '$eyecolour', '$height', '$weight', '$facialhair', '$hobbies', '$interest', '$aboutme', '$profession', '$food', '$music', '$tvshows', '$authors', '$movies', '$rolemodel', '$place', 0, 'y' , 'y')"; mysql_query($SQLQuery,$link); ?> '$email'. '$webpage' <-- shouldn't this be:'$email', (comma ;))'$webpage' laters, Fedde ~= Everybody lies, but it doesn't matter because nobody listens =~ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MYSQL Query problem...
is there something wrong with my SQL Query? I didnt get any results neither errors.. ') or die('Cannot connect to the database.'); mysql_select_db("profiles",$link); $SQLQuery = "INSERT INTO profyles (username, fname, mname, lname, address, country, gender, sexuality, status, bday, race, yahoo, msn, icq, email, webpage, hair, bodytype, eye, height, weight, facialhair, hobbies, interests, aboutme, profession, food, music, tvshows, authors, movies, rolemodel, place, visit, online, picture) VALUES ('$user', '$firstname', '$middlename', '$lastname ', '$address, '$country', '$gender', '$sexuality', '$status', '$birthday', '$race', '$yahoo', '$msn', '$icq', '$email'. '$webpage', 'haircolour', '$bodytype', '$eyecolour', '$height', '$weight', '$facialhair', '$hobbies', '$interest', '$aboutme', '$profession', '$food', '$music', '$tvshows', '$authors', '$movies', '$rolemodel', '$place', 0, 'y' , 'y')"; mysql_query($SQLQuery,$link); ?> ian
Re: [PHP-DB] MySQL query failing on apostrophe in data
if you're using php to generate this query you could use addslashes() to the piece that is holding 11301201 0603A HKA 3902 #3708_JD's AE Exp. this will escape the slashes making them \' similarly if you're getting \' in your return values you can use stripslashes() www.php.net/addslashes www.php.net/stripslashes hth jeff "Dillon, John" <[EMAIL PROTECTED]To: 'PHP-DB' <[EMAIL PROTECTED]> o.uk>cc: Subject: [PHP-DB] MySQL query failing on apostrophe in data 08/27/2003 12:59 PM How do I avoid the problem in the subject hereto? SELECT query uses variable in the WHERE clause. Fails on the following query: SELECT Tbl.fld FROM Tbl WHERE Tbl.fld2='11301201 0603A HKA 3902 #3708_JD's AE Exp' AND ... John http://www.cantor.com CONFIDENTIAL: This e-mail, including its contents and attachments, if any, are confidential. If you are not the named recipient please notify the sender and immediately delete it. You may not disseminate, distribute, or forward this e-mail message or disclose its contents to anybody else. Copyright and any other intellectual property rights in its contents are the sole property of Cantor Fitzgerald. E-mail transmission cannot be guaranteed to be secure or error-free. The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. Although we routinely screen for viruses, addressees should check this e-mail and any attachments for viruses. We make no representation or warranty as to the absence of viruses in this e-mail or any attachments. Please note that to ensure regulatory compliance and for the protection of our customers and business, we may monitor and read e-mails sent to and from our server(s). For further important information, please read the Important Legal Information and Legal Statement at http://www.cantor.com/legal_information.html -- 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] MySQL query failing on apostrophe in data
From: "Dillon, John" <[EMAIL PROTECTED]> > How do I avoid the problem in the subject hereto? SELECT query uses > variable in the WHERE clause. Fails on the following query: > > SELECT Tbl.fld FROM Tbl WHERE Tbl.fld2='11301201 0603A HKA 3902 #3708_JD's > AE Exp' AND ... Escape the single quote with a backslash: SELECT Tbl.fld FROM Tbl WHERE Tbl.fld2='11301201 0603A HKA 3902 #3708_JD\'s AE Exp' AND ... addslashes() works rather well for this. ---John Holmes... http://www.cantor.com CONFIDENTIAL: This e-mail, including its contents and attachments, if any, are confidential. If you are not the named recipient please notify the sender and immediately delete it. You may not disseminate, distribute, or forward this e-mail message or disclose its contents to anybody else. Copyright and any other intellectual property rights in its contents are the sole property of Cantor Fitzgerald. E-mail transmission cannot be guaranteed to be secure or error-free. The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. Although we routinely screen for viruses, addressees should check this e-mail and any attachments for viruses. We make no representation or warranty as to the absence of viruses in this e-mail or any attachments. Please note that to ensure regulatory compliance and for the protection of our customers and business, we may monitor and read e-mails sent to and from our server(s). For further important information, please read the Important Legal Information and Legal Statement at http://www.cantor.com/legal_information.html -- 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] MySQL query failing on apostrophe in data
try putting a \ before the apostrophe -Original Message- From: Dillon, John [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 27, 2003 12:59 PM To: 'PHP-DB' Subject: [PHP-DB] MySQL query failing on apostrophe in data How do I avoid the problem in the subject hereto? SELECT query uses variable in the WHERE clause. Fails on the following query: SELECT Tbl.fld FROM Tbl WHERE Tbl.fld2='11301201 0603A HKA 3902 #3708_JD's AE Exp' AND ... John http://www.cantor.com CONFIDENTIAL: This e-mail, including its contents and attachments, if any, are confidential. If you are not the named recipient please notify the sender and immediately delete it. You may not disseminate, distribute, or forward this e-mail message or disclose its contents to anybody else. Copyright and any other intellectual property rights in its contents are the sole property of Cantor Fitzgerald. E-mail transmission cannot be guaranteed to be secure or error-free. The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. Although we routinely screen for viruses, addressees should check this e-mail and any attachments for viruses. We make no representation or warranty as to the absence of viruses in this e-mail or any attachments. Please note that to ensure regulatory compliance and for the protection of our customers and business, we may monitor and read e-mails sent to and from our server(s). For further important information, please read the Important Legal Information and Legal Statement at http://www.cantor.com/legal_information.html -- 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] MySQL query failing on apostrophe in data
> How do I avoid the problem in the subject hereto? SELECT query uses > variable in the WHERE clause. Fails on the following query: > > SELECT Tbl.fld FROM Tbl WHERE Tbl.fld2='11301201 0603A HKA 3902 #3708_JD's > AE Exp' AND ... Escape it. SELECT Tbl.fld FROM Tbl WHERE Tbl.fld2='11301201 0603A HKA 3902 #3708_JD\'s AE Exp' AND ... Use mysql_escape_string() or addslashes() http://www.php.net/manual/en/function.mysql-escape-string.php http://www.php.net/manual/en/function.addslashes.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL query failing on apostrophe in data
How do I avoid the problem in the subject hereto? SELECT query uses variable in the WHERE clause. Fails on the following query: SELECT Tbl.fld FROM Tbl WHERE Tbl.fld2='11301201 0603A HKA 3902 #3708_JD's AE Exp' AND ... John http://www.cantor.com CONFIDENTIAL: This e-mail, including its contents and attachments, if any, are confidential. If you are not the named recipient please notify the sender and immediately delete it. You may not disseminate, distribute, or forward this e-mail message or disclose its contents to anybody else. Copyright and any other intellectual property rights in its contents are the sole property of Cantor Fitzgerald. E-mail transmission cannot be guaranteed to be secure or error-free. The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. Although we routinely screen for viruses, addressees should check this e-mail and any attachments for viruses. We make no representation or warranty as to the absence of viruses in this e-mail or any attachments. Please note that to ensure regulatory compliance and for the protection of our customers and business, we may monitor and read e-mails sent to and from our server(s). For further important information, please read the Important Legal Information and Legal Statement at http://www.cantor.com/legal_information.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Mysql Query
Hi guys, I have been working in php/oracle for quite some time now but recently had to shift myself to mysql and I am struggling with a particular query here. Scenario is, certain researchers can submit their proposals and those proposal are stored in a table researcher. proposals are of 2 types lets say type 1 and type 2. Later they can view their own submited proposals which is working fine, but there is one new condition now.What my client wants is that these researchers can view all proposals of type 2 but only those proposals of type 1 should be displayed to a researcher whose submit date is greater than a certain date lets say '2003-08-01'. Now how do I create a single query which can return me such results. I know there is a way with join but cant find a solution, with oracle I would have used sub query but that cannot be done on mysql so I need your guys help thanks Table name: researcher Concerned fields pid <-- porposal id rid <-- researcher id ptype <--- proposal type submitd <-- submission date. I would use rid = 42 and for ptype = 1 submitd should be greater than '2003-08-01' and there is no restriction of date with ptype =2. Thanks again. if there is something not clear feel free to ask
RE: [PHP-DB] MySQL Query help please!
urm, so it does.., thank you for your polite reply, I had included a month element in my query but left it out cos i didnt think it made a dufference, of course i was using a where month = 1 clause after the ON bit which was limiting the link to those records, duh! cheers Dan -Original Message- From: Mark [mailto:[EMAIL PROTECTED] Sent: 12 March 2003 15:36 To: Griffiths, Daniel Cc: [EMAIL PROTECTED] Subject: Re: [PHP-DB] MySQL Query help please! I just set up a test db using your info, and the query you have worked ok. It provided a zero. Here's what I have (slightly modified from yours, but not appreciably). select ports.port, sum(stats.amount) as Total from ports left join stats on ports.port=stats.to_port group by ports.port order by Total desc Data: Ports - UK France Germany Italy Stats - France 1000 Italy 3000 UK 2000 France 1000 UK 5000 Result: UK 7000 Italy 3000 France 2000 Germany 0 --- "Griffiths, Daniel" <[EMAIL PROTECTED]> wrote: > Hi all, > > I have 2 tables that I need to extract data from, say PORTS and > STATS. > > PORTS contains a list of Sea Ports in PORT i.e. : - > > PORTS.PORT > > UK > FRANCE > GERMANY > NETHERLANDS > SPAIN > ITALY > > STATS contains a few thousand records that with among other things > has the elements TO_PORT and AMOUNT. > > the entries in TO_PORT match the ports in PORT. > > Now one of the things I want to do is produce a summary of the > totals of AMOUNT against the ports in PORTS, including (and this is > the important bit that I'm stuck on) the ports in PORTS that do not > have a match in STATS.TO_PORT. so that I get a result such as : - > > UK300 > FRANCE100 > GERMANY 400 > NETHERLANDS 0 > SPAIN 0 > ITALY 300 > > and NOT :- > > UK300 > FRANCE100 > GERMANY 400 > ITALY 300 > > Which is all I can get at the moment. > > the query I am using is : - > > SELECT PORTS.PORT, SUM(STATS.AMOUNT) FROM PORTS LEFT JOIN STATS ON > PORTS.PORT = STATS.TO_PORT GROUP BY PORTS.PORT > > Basicaly what I want to do is pull out a list of all entries in > PORTS.PORT and put a total figure against it from STATS.AMOUNT > where the STATS.TO_PORT matches PORTS.PORT and just a zero if > theres no entry in STATS. > > Anyone got any ideas? > > Thanks > > Dan > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > = Mark Weinstock [EMAIL PROTECTED] *** You can't demand something as a "right" unless you are willing to fight to death to defend everyone else's right to the same thing. *** __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL Query help please!
I just set up a test db using your info, and the query you have worked ok. It provided a zero. Here's what I have (slightly modified from yours, but not appreciably). select ports.port, sum(stats.amount) as Total from ports left join stats on ports.port=stats.to_port group by ports.port order by Total desc Data: Ports - UK France Germany Italy Stats - France 1000 Italy 3000 UK 2000 France 1000 UK 5000 Result: UK 7000 Italy 3000 France 2000 Germany 0 --- "Griffiths, Daniel" <[EMAIL PROTECTED]> wrote: > Hi all, > > I have 2 tables that I need to extract data from, say PORTS and > STATS. > > PORTS contains a list of Sea Ports in PORT i.e. : - > > PORTS.PORT > > UK > FRANCE > GERMANY > NETHERLANDS > SPAIN > ITALY > > STATS contains a few thousand records that with among other things > has the elements TO_PORT and AMOUNT. > > the entries in TO_PORT match the ports in PORT. > > Now one of the things I want to do is produce a summary of the > totals of AMOUNT against the ports in PORTS, including (and this is > the important bit that I'm stuck on) the ports in PORTS that do not > have a match in STATS.TO_PORT. so that I get a result such as : - > > UK300 > FRANCE100 > GERMANY 400 > NETHERLANDS 0 > SPAIN 0 > ITALY 300 > > and NOT :- > > UK300 > FRANCE100 > GERMANY 400 > ITALY 300 > > Which is all I can get at the moment. > > the query I am using is : - > > SELECT PORTS.PORT, SUM(STATS.AMOUNT) FROM PORTS LEFT JOIN STATS ON > PORTS.PORT = STATS.TO_PORT GROUP BY PORTS.PORT > > Basicaly what I want to do is pull out a list of all entries in > PORTS.PORT and put a total figure against it from STATS.AMOUNT > where the STATS.TO_PORT matches PORTS.PORT and just a zero if > theres no entry in STATS. > > Anyone got any ideas? > > Thanks > > Dan > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > = Mark Weinstock [EMAIL PROTECTED] *** You can't demand something as a "right" unless you are willing to fight to death to defend everyone else's right to the same thing. *** __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL Query help please!
Hi all, I have 2 tables that I need to extract data from, say PORTS and STATS. PORTS contains a list of Sea Ports in PORT i.e. : - PORTS.PORT UK FRANCE GERMANY NETHERLANDS SPAIN ITALY STATS contains a few thousand records that with among other things has the elements TO_PORT and AMOUNT. the entries in TO_PORT match the ports in PORT. Now one of the things I want to do is produce a summary of the totals of AMOUNT against the ports in PORTS, including (and this is the important bit that I'm stuck on) the ports in PORTS that do not have a match in STATS.TO_PORT. so that I get a result such as : - UK 300 FRANCE 100 GERMANY 400 NETHERLANDS 0 SPAIN 0 ITALY 300 and NOT :- UK 300 FRANCE 100 GERMANY 400 ITALY 300 Which is all I can get at the moment. the query I am using is : - SELECT PORTS.PORT, SUM(STATS.AMOUNT) FROM PORTS LEFT JOIN STATS ON PORTS.PORT = STATS.TO_PORT GROUP BY PORTS.PORT Basicaly what I want to do is pull out a list of all entries in PORTS.PORT and put a total figure against it from STATS.AMOUNT where the STATS.TO_PORT matches PORTS.PORT and just a zero if theres no entry in STATS. Anyone got any ideas? Thanks Dan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MySQL Query Question
> I need some help from you MySQL gurus out there. This is pure SQL by the > way..no front-end. > > > Lets say you have a table that has 2columns.columnA, > ColumnB and columnC > I have something like this Oh CRAP! You lost me... you said two columns but listed 3! ;) > if (columnA>'7', (columnB/columnC) + 52, columnB/ColumnC) as "Number" I think someone already suggested these, but either include a WHERE c != 0 in your WHERE clause (if you don't want those rows), or use a nested IF() to return a specific value when C is zero: IF(ColumnC=0,'Undef',IF(ColumnA>7,ColumnB/ColumnC+52,ColumnB/ColumnC)) ---John W. Holmes... PHP Architect - A monthly magazine for PHP Professionals. Get your copy today. http://www.phparch.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL Query Question
It is safe to test if the divisor is zero: replace columnB/columnC by IF( columnC = 0, 0, columnB/columnC ) (the "0" could be anything else; this depends on your business logic) Ignatius - Original Message - From: "Jeremy" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, January 23, 2003 10:29 PM Subject: [PHP-DB] MySQL Query Question > Hello, > I need some help from you MySQL gurus out there. This is pure SQL by the > way..no front-end. > > > Lets say you have a table that has 2columns.columnA, > ColumnB and columnC > I have something like this > > if (columnA>'7', (columnB/columnC) + 52, columnB/ColumnC) as "Number" > > however i need to test if columnc is 0 also cause if i need divide by zero i > get a null value > > I cant figure out how to do this any help would be greatly appreciated > > > -- > 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] MySQL Query Question
Jeremy, This question probably should be posted to the MySql mailing list but here goes. Take a look at this link: http://www.mysql.com/doc/en/Where_optimisations.html Your example was not very explicit so I am not sure, but I believe you are in need of a WHERE clause. For example; Select a, b, c where a >7 and c <> 0 This should get you going in the right direction. At 03:29 PM 1/23/03 -0600, Jeremy wrote: Hello, I need some help from you MySQL gurus out there. This is pure SQL by the way..no front-end. Lets say you have a table that has 2columns.columnA, ColumnB and columnC I have something like this if (columnA>'7', (columnB/columnC) + 52, columnB/ColumnC) as "Number" however i need to test if columnc is 0 also cause if i need divide by zero i get a null value I cant figure out how to do this any help would be greatly appreciated -- 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] MySQL Query Question
Hello, I need some help from you MySQL gurus out there. This is pure SQL by the way..no front-end. Lets say you have a table that has 2columns.columnA, ColumnB and columnC I have something like this if (columnA>'7', (columnB/columnC) + 52, columnB/ColumnC) as "Number" however i need to test if columnc is 0 also cause if i need divide by zero i get a null value I cant figure out how to do this any help would be greatly appreciated -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] [mysql] query atomicity
i am unfarmillar with InnoDB, i am currently using MyISAM i believe. just the default one. i am a complete noobie with InnoDB, do i have to convert my db? cheers "Andrey Hristov" <[EMAIL PROTECTED]> wrote in message 014d01c2540b$d309bf40$1601a8c0@andreywin">news:014d01c2540b$d309bf40$1601a8c0@andreywin... > Choices > 1) Use InnoDB > 2) If you cannot use innodb than you can write your code in the way you add > in an array > queries that will rollback the change : > > if you do > $r1 = mysql_query('insert into some_table (k1,k2) values (4,2);'); > $r1 = mysql_query('insert into some_table (k1,k2) values (5,3);'); > > you must have in the rollback array this > $rollback_ar = array('DELETE FROM some_table WHERE k1=4 and k2=2;', > 'DELETE FROM some_table WHERE k1=5 and k2=3'); > > Hope this is clear. > > Best regards > Andrey Hristov > > > - Original Message ----- > From: ":B nerdy" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Wednesday, September 04, 2002 3:04 PM > Subject: [PHP-DB] [mysql] query atomicity > > > > how would i go about making a few queries into a transaction - that is, if > > one of them fails, the previous queries get rolled back... > > > > cheers > > > > > > > > > > -- > > 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] [mysql] query atomicity
Choices 1) Use InnoDB 2) If you cannot use innodb than you can write your code in the way you add in an array queries that will rollback the change : if you do $r1 = mysql_query('insert into some_table (k1,k2) values (4,2);'); $r1 = mysql_query('insert into some_table (k1,k2) values (5,3);'); you must have in the rollback array this $rollback_ar = array('DELETE FROM some_table WHERE k1=4 and k2=2;', 'DELETE FROM some_table WHERE k1=5 and k2=3'); Hope this is clear. Best regards Andrey Hristov - Original Message - From: ":B nerdy" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, September 04, 2002 3:04 PM Subject: [PHP-DB] [mysql] query atomicity > how would i go about making a few queries into a transaction - that is, if > one of them fails, the previous queries get rolled back... > > cheers > > > > > -- > 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] [mysql] query atomicity
how would i go about making a few queries into a transaction - that is, if one of them fails, the previous queries get rolled back... cheers -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] [mysql] query wierness :/
this function doesnt work function query($sql) { return mysql_query($sql, $this->mysql_link) or die("query failed: $sql"); } but this one does function query($sql) { $mysql_result = mysql_query($sql, $this->mysql_link) or die("query failed: $sql"); return $mysql_result; } why does it matter?? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL Query problem...
I seem to have run into another problem with a query that I am doing exactly like the example in the MySQL documentation, I think. The following query is not working on my PHP page, and it is not working from a MySQL command line either. This looks exactly the same as an example in the MySQL documentation though. Thanks in advance for the help again. SELECT * FROM oncall WHERE TO_DAYS(NOW()) - TO_DAYS(ptime) <= 3 This is the query that I am ultimately after: SELECT * FROM oncall ORDER BY 'ptime' ASC WHERE TO_DAYS(NOW()) - TO_DAYS(ptime) <= 3 I am trying to display all of the records from the past three days. This looks like the way to go about doing this, but if there is a better way please let me know. (Just as more info, the 'ptime' field is a datetime type of field.) Scott Nipp Phone: (214) 858-1289 E-mail: [EMAIL PROTECTED] Web: http:\\ldsa.sbcld.sbc.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL Query Acting Up
I would suggest using ' instead of " for SQL queries... That's what I use... so much easier. $query = "UPDATE articles2 SET title='$title', brief='$brief', doclink='$doclink' WHERE article_id='$id'"; Then I would print the query: print( "$query\n" ); Then if it still looks right, try manually typing it in a CLI interface. -w -- William Fong - [EMAIL PROTECTED] Phone: 626.968.6424 x210 | Fax: 626.968.6877 Wireless #: 714.315.8954| Wireless E-mail: - Original Message - From: "Martin Clifford" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, June 20, 2002 10:03 AM Subject: [PHP-DB] MySQL Query Acting Up Howdy all, If someone call tell me why this MySQL query is not working, I'd be VERY thankful. I don't see a damn thing wrong with it, but I could have been looking at it too long. Here is the query: $query = "UPDATE articles2 SET title=\"$title", brief=\"$brief\", doclink=\"$doclink\" WHERE article_id=\"$id\""; $result = mysql_query($query, $connect) or die(mysql_error()); No errors are produced, yet the query doesn't do anything. Nothing in the row changes. Any help would be appreciated! Martin -- 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] MySQL Query Acting Up
Howdy all, If someone call tell me why this MySQL query is not working, I'd be VERY thankful. I don't see a damn thing wrong with it, but I could have been looking at it too long. Here is the query: $query = "UPDATE articles2 SET title=\"$title", brief=\"$brief\", doclink=\"$doclink\" WHERE article_id=\"$id\""; $result = mysql_query($query, $connect) or die(mysql_error()); No errors are produced, yet the query doesn't do anything. Nothing in the row changes. Any help would be appreciated! Martin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL Query
At 1:01 PM +0100 5/3/02, CM wrote: >SELECT Periode.LibPeriode, PaysPeriode.RefPaysPeriode, >PaysPeriode.DateDebut,PaysPeriode.DateFin >FROM Periode, PaysPeriode, Pays >WHERE Pays.RefPays = PaysPeriode.Refpays >AND Periode.RefPeriode = PaysPeriode.RefPeriode >AND PaysPeriode.DateDebut > 2002020700 >AND Pays.RefPays = 1 >ORDER BY Periode.libPeriode, PaysPeriode.DateDebut > >This is soo close to working but just not quite. The Access query just >pulled out unique values for the Periode.LibPeriod field which was perfect. >But the MySQL seems to pull them all out. I have tried GROUPING the result >by the Periode.LibPeriode which gives me unique values in the field but I >need the unique values with the highest startdate i.e. PaysPeriode.DateDebut >but it always pulls out the first start date? I'd add a Max() function with the Group By, like... SELECT Periode.LibPeriode, PaysPeriode.RefPaysPeriode, MAX(PaysPeriode.DateDebut), PaysPeriode.DateFin FROM Periode, PaysPeriode, Pays WHERE Pays.RefPays = PaysPeriode.Refpays AND Periode.RefPeriode = PaysPeriode.RefPeriode AND PaysPeriode.DateDebut > 2002020700 AND Pays.RefPays = 1 GROUP BY Periode.libPeriode ORDER BY Periode.libPeriode, PaysPeriode.DateDebut I'm not certain why your Access query only pulled one record. From the skim, it seems as if it should pull all that match, since you didn't indicate you wanted only those distinctly, or a max value. Though often an Inner Join can act as a limiter as well, so that maybe why it worked. But anyway, the find all records, max or min of the value you want, group records (max, min, ave, count , etc. functions all REQUIRE the Group By, you'll generate an error if you forget it), then order them, should work. Alnisa -- . Alnisa Allgood Executive Director Nonprofit Tech (ph) 415.337.7412 (fx) 415.337.7927 (url) http://www.nonprofit-techworld.org (url) http://www.nonprofit-tech.org (url) http://www.tech-library.org . Nonprofit Tech E-Update mailto:[EMAIL PROTECTED] . transforming nonprofits through technology . -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL Query
Hi. I am having trouble after migrating an Access Database to MySQL. Basically the Access query I was using for my application was: SELECT Periode.LibPeriode, PaysPeriode.RefPaysPeriode, PaysPeriode.DateDebut, PaysPeriode.DateFin FROM Periode INNER JOIN (Pays INNER JOIN PaysPeriode ON Pays.RefPays = PaysPeriode.RefPays) ON Periode.RefPeriode = PaysPeriode.RefPeriode WHERE ((Pays.RefPays = 1) AND (PaysPeriode.DateDebut>#7/2/02#)) ORDER BY Periode.libPeriode, PaysPeriode.DateDebut However this wouldn't work in MySQL so I altered it to read... SELECT Periode.LibPeriode, PaysPeriode.RefPaysPeriode, PaysPeriode.DateDebut,PaysPeriode.DateFin FROM Periode, PaysPeriode, Pays WHERE Pays.RefPays = PaysPeriode.Refpays AND Periode.RefPeriode = PaysPeriode.RefPeriode AND PaysPeriode.DateDebut > 2002020700 AND Pays.RefPays = 1 ORDER BY Periode.libPeriode, PaysPeriode.DateDebut This is soo close to working but just not quite. The Access query just pulled out unique values for the Periode.LibPeriod field which was perfect. But the MySQL seems to pull them all out. I have tried GROUPING the result by the Periode.LibPeriode which gives me unique values in the field but I need the unique values with the highest startdate i.e. PaysPeriode.DateDebut but it always pulls out the first start date? Can anyone help? Thanks, Chris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL query help - limit and group by
Hi there. I would like to return the 2 most recent events from my table, for each author (Author_ID). Here's my table structure (simplified). tblEvents ID Title Create_Date Author_ID Is there a way to do this with a query instead of getting every record, sorted by Author_ID, displaying the first two events, and skipping through until the Author_ID changes, then displaying the first 2 events for that Author_ID, etc. I know I could use a GROUP BY clause, but can I limit the number of records returned in each group, sorted by date? -- Faye -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] mysql query file
Hello. I am wishing to store all mysql querys in a single file and when they needed to be ran call them using include "mysql.php"; But I keep getting errors saying that its an invalid number of mysql resoruces or something like that when I bring the query into the atuual file and dont run it from the extra one it works perfectly. Anyone know how to do it right ? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] MYSQL query help
It's not the IN, it's the sub-query you cannot use. MySQL doesn't support them and it's just about the main reason I don't like it. Alternatives? You could execute your subquery and return the results to an array. Loop through the array, using the index and the indexed element to drive a series of queries, accumulating your results as you go. If the number of items return by the subquery is not large, you could build an "in set", for lack of a better term, so you would end up with " ... and IN ("first", "second", ..."nth") ". (Check syntax!!) But that's likely to be slower than cold molasses and run like a pig. If you're not too far into the project, and you will have a lot of subqueries, switch to PostgreSQL or a database that supports them. I don't really have an answer. I hope someone comes up with a more elegant solution. Regards - Miles Thompson At 09:34 AM 12/14/2001 -0500, Harpreet wrote: >I dont think we can use 'IN' and 'NOT IN' in mysql. Is there an alternative >that would work? > >select * from lib_asset_tbl where material_id <>'' and asset_id in (select >asset_id from lib_copy_tbl) > >Help is appreciated. > >Regards, >Harpreet Kaur >Software Developer >Crispin Corporations Inc. > > > >-- >PHP Database Mailing List (http://www.php.net/) >To unsubscribe, e-mail: [EMAIL PROTECTED] >For additional commands, e-mail: [EMAIL PROTECTED] >To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] MYSQL query help
I dont think we can use 'IN' and 'NOT IN' in mysql. Is there an alternative that would work? select * from lib_asset_tbl where material_id <>'' and asset_id in (select asset_id from lib_copy_tbl) Help is appreciated. Regards, Harpreet Kaur Software Developer Crispin Corporations Inc. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] MySQL Query
On Sat, 27 Oct 2001 13:36:57 +1000 "Andrew Duck" <[EMAIL PROTECTED]> wrote: > Error > SQL-query : > > CREATE TABLE mail ( > from int(32) NOT NULL default '0', > to int(32) NOT NULL default '0', > subject varchar(80) NOT NULL default '', > message text NOT NULL > ) TYPE=MyISAM > > > MySQL said: > > > You have an error in your SQL syntax near 'from int(32) NOT NULL default '0', > to int(32) NOT NULL default '0', > subjec' at line 2 > > > Can someone please point out my error in the above query.. > Thanks > > Reading the list of reserved words in MySQL, I find the two words "from" and "to". Perhaps trying "mail_from" and "mail_to" (or whatever prefix you would like) would work better? -- Raquel It is easy to take liberty for granted when you have never had it taken from you. --M. Grundler -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] MySQL Query
Error SQL-query : CREATE TABLE mail ( from int(32) NOT NULL default '0', to int(32) NOT NULL default '0', subject varchar(80) NOT NULL default '', message text NOT NULL ) TYPE=MyISAM MySQL said: You have an error in your SQL syntax near 'from int(32) NOT NULL default '0', to int(32) NOT NULL default '0', subjec' at line 2 Can someone please point out my error in the above query.. Thanks
Re: [PHP-DB] mySQL Query and blank fields
On Thursday 25 October 2001 15:08, Rick Emery wrote: > First, I wouldn't use length(), I'd use REGEXP: SELECT * FROM > foo_table WHERE fieldname REGEXP '..+' > It's quicker. regular expression is faster then a length() call? that's seems strange considering how complex a regexp can be with regexp, it takes 0.13sec on a simple table with lenght it is 0.08sec. Arpi -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] mySQL Query and blank fields
Try: UPDATE mytable SET myfield=CONCAT( LEFT(myfield,LENGTH(myfield)-1),'new_string') WHERE some_condition If you know the field will ALWAYS be the same length (for instance 5, and you wish to replace the 5th character): UPDATE mytable SET myfield=CONCAT(LEFT(myfield,4),'new_char') WHERE some_condition -Original Message- From: Niklas Lampén [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 25, 2001 8:37 AM To: Php-DB Subject: RE: [PHP-DB] mySQL Query and blank fields I'd like to do it in mySQL, I think it will be done much quicker since it a large database. The type of the field is allready varchar. Niklas -Original Message- From: Rick Emery [mailto:[EMAIL PROTECTED]] Sent: 25. lokakuuta 2001 16:08 To: Php-DB Subject: RE: [PHP-DB] mySQL Query and blank fields First, I wouldn't use length(), I'd use REGEXP: SELECT * FROM foo_table WHERE fieldname REGEXP '..+' It's quicker. As far as the second question, do you wish to do it in PHP or MySQL? Does the value in MySQL require other computations (so, it must remain numeric) or can it be a CHAR string? -Original Message- From: Niklas Lampén [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 25, 2001 8:04 AM To: Php-DB Subject: RE: [PHP-DB] mySQL Query and blank fields Thanks a lot! I quite didn't know what to look for in the manual. I actually have another question: If I want to update several fields, add a number to the end of the fields value, how do I do that? I have field with value '9876' and if I do "...field=field+'5'..." the value turns into '9881' not '98765' as I expected. Niklas -Original Message----- From: Rick Emery [mailto:[EMAIL PROTECTED]] Sent: 25. lokakuuta 2001 15:56 To: Php-DB Subject: RE: [PHP-DB] mySQL Query and blank fields Change that to: SELECT * FROM foo_table WHERE length(bar_field)>1; -Original Message- From: Tamas Arpad [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 25, 2001 8:01 AM To: Niklas Lampén; Php-DB Subject: Re: [PHP-DB] mySQL Query and blank fields On Thursday 25 October 2001 13:16, Niklas Lampén wrote: > How can I query for specified fields that has to contain atleast 2 > charters? SELECT * FROM foo_table WHERE length(bar_field)>=1; Arpi > > Example: > Field AAA contains '' (NULL) => No match > Field BBB contains ' ' (one charter) => No match > Field CCC contains 'ab' => Match > Field DDD contains 'abcdefg' => Match > > > Niklas -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] mySQL Query and blank fields
I'd like to do it in mySQL, I think it will be done much quicker since it a large database. The type of the field is allready varchar. Niklas -Original Message- From: Rick Emery [mailto:[EMAIL PROTECTED]] Sent: 25. lokakuuta 2001 16:08 To: Php-DB Subject: RE: [PHP-DB] mySQL Query and blank fields First, I wouldn't use length(), I'd use REGEXP: SELECT * FROM foo_table WHERE fieldname REGEXP '..+' It's quicker. As far as the second question, do you wish to do it in PHP or MySQL? Does the value in MySQL require other computations (so, it must remain numeric) or can it be a CHAR string? -Original Message- From: Niklas Lampén [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 25, 2001 8:04 AM To: Php-DB Subject: RE: [PHP-DB] mySQL Query and blank fields Thanks a lot! I quite didn't know what to look for in the manual. I actually have another question: If I want to update several fields, add a number to the end of the fields value, how do I do that? I have field with value '9876' and if I do "...field=field+'5'..." the value turns into '9881' not '98765' as I expected. Niklas -Original Message- From: Rick Emery [mailto:[EMAIL PROTECTED]] Sent: 25. lokakuuta 2001 15:56 To: Php-DB Subject: RE: [PHP-DB] mySQL Query and blank fields Change that to: SELECT * FROM foo_table WHERE length(bar_field)>1; -Original Message- From: Tamas Arpad [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 25, 2001 8:01 AM To: Niklas Lampén; Php-DB Subject: Re: [PHP-DB] mySQL Query and blank fields On Thursday 25 October 2001 13:16, Niklas Lampén wrote: > How can I query for specified fields that has to contain atleast 2 > charters? SELECT * FROM foo_table WHERE length(bar_field)>=1; Arpi > > Example: > Field AAA contains '' (NULL) => No match > Field BBB contains ' ' (one charter) => No match > Field CCC contains 'ab' => Match > Field DDD contains 'abcdefg' => Match > > > Niklas -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] mySQL Query and blank fields
First, I wouldn't use length(), I'd use REGEXP: SELECT * FROM foo_table WHERE fieldname REGEXP '..+' It's quicker. As far as the second question, do you wish to do it in PHP or MySQL? Does the value in MySQL require other computations (so, it must remain numeric) or can it be a CHAR string? -Original Message- From: Niklas Lampén [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 25, 2001 8:04 AM To: Php-DB Subject: RE: [PHP-DB] mySQL Query and blank fields Thanks a lot! I quite didn't know what to look for in the manual. I actually have another question: If I want to update several fields, add a number to the end of the fields value, how do I do that? I have field with value '9876' and if I do "...field=field+'5'..." the value turns into '9881' not '98765' as I expected. Niklas -Original Message- From: Rick Emery [mailto:[EMAIL PROTECTED]] Sent: 25. lokakuuta 2001 15:56 To: Php-DB Subject: RE: [PHP-DB] mySQL Query and blank fields Change that to: SELECT * FROM foo_table WHERE length(bar_field)>1; -Original Message- From: Tamas Arpad [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 25, 2001 8:01 AM To: Niklas Lampén; Php-DB Subject: Re: [PHP-DB] mySQL Query and blank fields On Thursday 25 October 2001 13:16, Niklas Lampén wrote: > How can I query for specified fields that has to contain atleast 2 > charters? SELECT * FROM foo_table WHERE length(bar_field)>=1; Arpi > > Example: > Field AAA contains '' (NULL) => No match > Field BBB contains ' ' (one charter) => No match > Field CCC contains 'ab' => Match > Field DDD contains 'abcdefg' => Match > > > Niklas -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] mySQL Query and blank fields
>I have field with value '9876' and if I do "...field=field+'5'..." the >value turns into '9881' not '98765' as I expected. make your field type "var char" - Original Message - From: "Niklas Lampén" <[EMAIL PROTECTED]> To: "Php-DB" <[EMAIL PROTECTED]> Sent: Thursday, October 25, 2001 3:03 PM Subject: RE: [PHP-DB] mySQL Query and blank fields Thanks a lot! I quite didn't know what to look for in the manual. I actually have another question: If I want to update several fields, add a number to the end of the fields value, how do I do that? I have field with value '9876' and if I do "...field=field+'5'..." the value turns into '9881' not '98765' as I expected. Niklas -Original Message- From: Rick Emery [mailto:[EMAIL PROTECTED]] Sent: 25. lokakuuta 2001 15:56 To: Php-DB Subject: RE: [PHP-DB] mySQL Query and blank fields Change that to: SELECT * FROM foo_table WHERE length(bar_field)>1; -----Original Message- From: Tamas Arpad [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 25, 2001 8:01 AM To: Niklas Lampén; Php-DB Subject: Re: [PHP-DB] mySQL Query and blank fields On Thursday 25 October 2001 13:16, Niklas Lampén wrote: > How can I query for specified fields that has to contain atleast 2 > charters? SELECT * FROM foo_table WHERE length(bar_field)>=1; Arpi > > Example: > Field AAA contains '' (NULL) => No match > Field BBB contains ' ' (one charter) => No match > Field CCC contains 'ab' => Match > Field DDD contains 'abcdefg' => Match > > > Niklas -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] mySQL Query and blank fields
Thanks a lot! I quite didn't know what to look for in the manual. I actually have another question: If I want to update several fields, add a number to the end of the fields value, how do I do that? I have field with value '9876' and if I do "...field=field+'5'..." the value turns into '9881' not '98765' as I expected. Niklas -Original Message- From: Rick Emery [mailto:[EMAIL PROTECTED]] Sent: 25. lokakuuta 2001 15:56 To: Php-DB Subject: RE: [PHP-DB] mySQL Query and blank fields Change that to: SELECT * FROM foo_table WHERE length(bar_field)>1; -Original Message- From: Tamas Arpad [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 25, 2001 8:01 AM To: Niklas Lampén; Php-DB Subject: Re: [PHP-DB] mySQL Query and blank fields On Thursday 25 October 2001 13:16, Niklas Lampén wrote: > How can I query for specified fields that has to contain atleast 2 > charters? SELECT * FROM foo_table WHERE length(bar_field)>=1; Arpi > > Example: > Field AAA contains '' (NULL) => No match > Field BBB contains ' ' (one charter) => No match > Field CCC contains 'ab' => Match > Field DDD contains 'abcdefg' => Match > > > Niklas -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]