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 kr...@us.ibm.com 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 erosenb...@hygeiabiomedical.com wrote on 08/21/2013
11:59:19 PM:
From: Ethan Rosenberg erosenb...@hygeiabiomedical.com
To: Daniel Krook/White Plains/IBM@IBMUS
Cc: PHP Database List php-db@lists.php.net
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 erosenb...@hygeiabiomedical.com wrote on 08/21/
2013 07:48:12 PM:
From: Ethan Rosenberg erosenb...@hygeiabiomedical.com
To: PHP Database List php-db@lists.php.net
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 '$sql2br /';
print_r($sql2);
echo br /$upc $qnt $mnf $itm $odrpt $opf
$stkkbr /;
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
$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 mikeoki@gmail.com 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 kr...@us.ibm.com 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 erosenb...@hygeiabiomedical.com wrote on 08/21/2013
11:59:19 PM:
From: Ethan Rosenberg erosenb...@hygeiabiomedical.com
To: Daniel Krook/White Plains/IBM@IBMUS
Cc: PHP Database List php-db@lists.php.net
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 erosenb...@hygeiabiomedical.com wrote on 08/21/
2013 07:48:12 PM:
From: Ethan Rosenberg erosenb...@hygeiabiomedical.com
To: PHP Database List php-db@lists.php.net
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 '$sql2br /';
print_r($sql2);
echo br /$upc $qnt $mnf $itm $odrpt
$opf
$stkkbr /;
if (!$result2
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 '$sql2br /';
print_r($sql2);
echo br /$upc $qnt $mnf $itm $odrpt $opf
$stkkbr /;
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 '$sql2br /';
print_r($sql2);
echo br /$upc $qnt $mnf $itm $odrpt $opf
$stkkbr /;
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
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 erosenb...@hygeiabiomedical.com wrote on 08/21/2013
07:48:12 PM:
From: Ethan Rosenberg erosenb...@hygeiabiomedical.com
To: PHP Database List php-db@lists.php.net
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 '$sql2br /';
print_r($sql2);
echo br /$upc $qnt $mnf $itm $odrpt $opf
$stkkbr /;
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
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 erosenb...@hygeiabiomedical.com wrote on 08/21/2013
11:59:19 PM:
From: Ethan Rosenberg erosenb...@hygeiabiomedical.com
To: Daniel Krook/White Plains/IBM@IBMUS
Cc: PHP Database List php-db@lists.php.net
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 erosenb...@hygeiabiomedical.com wrote on 08/21/
2013 07:48:12 PM:
From: Ethan Rosenberg erosenb...@hygeiabiomedical.com
To: PHP Database List php-db@lists.php.net
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 '$sql2br /';
print_r($sql2);
echo br /$upc $qnt $mnf $itm $odrpt $opf
$stkkbr /;
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
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
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 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 tg-...@gryffyndevelopment.com 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 ron.pigg...@actsministries.org To: PHP DB php-db@lists.php.net 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
RE: [PHP-DB] MySQL query executes outside of PHP, but not in PHP
snip
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 'h3 class=H3-OTMSMain Input Files/h3';
do {
if ($result = $client2-use_result()) {
while ($input = $result-fetch_row()) {
$filename = $input[0];
$descr = $input[1];
echo 'pspan class=FileName'.$filename.'/span'.'
'.$descr.'/p';
}
$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
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: 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
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
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
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
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 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
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] php-db@lists.php.net 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
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:
?php
$conn = @mysql_connect(localhost, mysql,
ullneverguessit);
if (!$conn) {
echo( PNo se pudo conectar .
al servidor MySQL./P );
exit();
}
if (! @mysql_select_db(llamadas) ) {
echo( PNo se puede encontrar .
la base de datos clientes!/P );
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
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
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 table 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...
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 table 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...
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 table 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...
um, the errors might be helpful bastien From: NIPP, SCOTT V (SBCSI) [EMAIL PROTECTED] To: php-db@lists.php.net 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
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
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
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 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, 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
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
Re: [PHP-DB] MySql query
snip SELECT * FROM sometable Just that :) (do I hear you slapping your forehead? :) Bruno Ferreira. /snip 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
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
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... 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
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 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
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
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 problem...
is there something wrong with my SQL Query? I didnt get any results
neither errors..
?php
$link = mysql_connect('localhost', 'username') 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
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 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
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
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
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.ukcc: 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 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!
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 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
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
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(ColumnA7,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 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
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
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]
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]
Re: [PHP-DB] mySQL Query and blank fields
Assuming you're using MySQL - 35 seconds is all it took to find: http://www.mysql.com/doc/P/a/Pattern_matching.html Good luck! Russ. On Thu, 25 Oct 2001 14:16:40 +0300 =?iso-8859-1?Q?Niklas_Lamp=E9n?= [EMAIL PROTECTED] wrote: How can I query for specified fields that has to contain atleast 2 charters? 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 #---# Believe nothing - consider everything Russ Michell Anglia Polytechnic University Webteam Room 1C 'The Eastings' East Road, Cambridge e: [EMAIL PROTECTED] w: www.apu.ac.uk/webteam www.theruss.com #---# -- 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
REGEXP '..+' -Original Message- From: Niklas Lampén [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 25, 2001 6:17 AM To: Php-DB Subject: [PHP-DB] mySQL Query and blank fields How can I query for specified fields that has to contain atleast 2 charters? 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]
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]
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]
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]
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
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 Weirdness
easier solution:
while ($line = mysql_fetch_array($result)) {
print \ttr\n\t\ttd.$line['first_name'].\n\t/tr\n;
}
-Original Message-
From: Steve Cayford [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 20, 2001 5:16 PM
To: Chris S.
Cc: [EMAIL PROTECTED]
Subject: Re: [PHP-DB] MySQL Query Weirdness
When you use mysql_fetch_array you're actually retrieving an array with
two copies of the value you're looking for. One is indexed by the column
number and one indexed by the column name. So it looks like you're
looping through them both and printing each out. You might want
mysql_fetch_assoc (to use just the column name) or mysql_fetch_row (to
use just the column number)
-Steve
On Thursday, September 20, 2001, at 05:02 PM, Chris S. wrote:
Hello,
I'm new at this php/mysql stuff, so go easy on my guys.
I've got my query script up and working, but the problem is I'm getting
the
same column printed twice on the html output. Here is the output:
Connected successfully
Chris Chris
Mark Mark
Mike Mike
Dee Dee
etc...
Here is my .php script:
?php
$link = mysql_connect(localhost, dbuser,dbpassword)
or die (Could not connect);
print (Connected successfully);
mysql_select_db (dapscores) or die (Could not select database);
$query = SELECT first_name FROM overall_results;
$result = mysql_query ($query) or die (Query failed);
// printing HTML result
print table\n;
while ($line = mysql_fetch_array($result)) {
print \ttr\n;
while(list($col_name, $col_value) = each($line)) {
print \t\ttd$col_value/td\n;
}
print \t/tr\n;
}
print /table\n;
mysql_close($link);
?
Here is my database layout:
mysql describe overall_results;
+---+-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-+---+
| Match_Date| date| YES | | NULL| |
| Place | varchar(10) | YES | | NULL| |
| Last_Name | varchar(20) | YES | | NULL| |
| First_Name| varchar(20) | YES | | NULL| |
| USPSA | varchar(10) | YES | | NULL| |
| Class | char(3) | YES | | NULL| |
| Division | varchar(20) | YES | | NULL| |
| PF| varchar(7) | YES | | NULL| |
| Lady | char(3) | YES | | NULL| |
| Mil | varchar(4) | YES | | NULL| |
| Law | varchar(4) | YES | | NULL| |
| F0r | char(3) | YES | | NULL| |
| Age | varchar(20) | YES | | NULL| |
| Match_Pts | float | YES | | NULL| |
| Match_percent | float | YES | | NULL| |
+---+-+--+-+-+---+
If I run the query from mysql, it works fine, just the HTML output
shows
the double column thing. Is this a database problem? I've tried
different
variations of my script and I get the same output each time.
Thanks
--
Chris S.
[EMAIL PROTECTED]
PGP 0xDA39672B
--
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 Weirdness
When you use mysql_fetch_array you're actually retrieving an array with
two copies of the value you're looking for. One is indexed by the column
number and one indexed by the column name. So it looks like you're
looping through them both and printing each out. You might want
mysql_fetch_assoc (to use just the column name) or mysql_fetch_row (to
use just the column number)
-Steve
On Thursday, September 20, 2001, at 05:02 PM, Chris S. wrote:
Hello,
I'm new at this php/mysql stuff, so go easy on my guys.
I've got my query script up and working, but the problem is I'm getting
the
same column printed twice on the html output. Here is the output:
Connected successfully
Chris Chris
Mark Mark
Mike Mike
Dee Dee
etc...
Here is my .php script:
?php
$link = mysql_connect(localhost, dbuser,dbpassword)
or die (Could not connect);
print (Connected successfully);
mysql_select_db (dapscores) or die (Could not select database);
$query = SELECT first_name FROM overall_results;
$result = mysql_query ($query) or die (Query failed);
// printing HTML result
print table\n;
while ($line = mysql_fetch_array($result)) {
print \ttr\n;
while(list($col_name, $col_value) = each($line)) {
print \t\ttd$col_value/td\n;
}
print \t/tr\n;
}
print /table\n;
mysql_close($link);
?
Here is my database layout:
mysql describe overall_results;
+---+-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-+---+
| Match_Date| date| YES | | NULL| |
| Place | varchar(10) | YES | | NULL| |
| Last_Name | varchar(20) | YES | | NULL| |
| First_Name| varchar(20) | YES | | NULL| |
| USPSA | varchar(10) | YES | | NULL| |
| Class | char(3) | YES | | NULL| |
| Division | varchar(20) | YES | | NULL| |
| PF| varchar(7) | YES | | NULL| |
| Lady | char(3) | YES | | NULL| |
| Mil | varchar(4) | YES | | NULL| |
| Law | varchar(4) | YES | | NULL| |
| F0r | char(3) | YES | | NULL| |
| Age | varchar(20) | YES | | NULL| |
| Match_Pts | float | YES | | NULL| |
| Match_percent | float | YES | | NULL| |
+---+-+--+-+-+---+
If I run the query from mysql, it works fine, just the HTML output
shows
the double column thing. Is this a database problem? I've tried
different
variations of my script and I get the same output each time.
Thanks
--
Chris S.
[EMAIL PROTECTED]
PGP 0xDA39672B
--
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 problem
on 7/26/01 11:11 AM, Steve Fitzgerald ([EMAIL PROTECTED]) wrote: ran the query in the MySQL client and got the ERROR 1054: Unknown column '$ContactID' in 'where clause'. I know the column ContactID exists. Of course it did. It can't find the text $ContactID in the table. Try putting in a number (1,2,3, etc.) and see if it works. If it does, try echo'ing out the $ContactID and the $sql to the screen. See if $ContactID really is set and then try the output of $sql in the MySQL client. ?php echo 'ContactID is: ' , $ContactID, 'br'; $sql=SELECT * FROM contacts WHERE ContactID='$ContactID'; echo 'SQL query is: ' , $sql, 'br'; $result = mysql_query($sql,$db); printf(%s\n, mysql_result($result,FirstName)); printf(%sbr\n, mysql_result($result,LastName)); ? Hope that helps. Sincerely, Paul Burney +-+-+ | Paul Burney | P: 310.825.8365 | | Webmaster Programmer | E: [EMAIL PROTECTED] | | UCLA - GSEIS - ETU | W: http://www.gseis.ucla.edu/ | +-+-+ -- 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]
