Re: [PHP-DB] mysql query

2013-08-22 Thread Michael Oki
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

2013-08-22 Thread Vinay Kannan
 $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

2013-08-21 Thread Toby Hart Dyke


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

2013-08-21 Thread Ethan Rosenberg

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

2013-08-21 Thread Daniel Krook
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

2013-08-21 Thread Daniel Krook
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

2009-12-14 Thread Chris

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

2009-12-14 Thread ron . piggott

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

2009-11-14 Thread Ron Piggott

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

2009-01-26 Thread Dave.McGovern

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

2009-01-23 Thread Peter Beckman

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

2008-10-31 Thread Thodoris



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

2008-10-15 Thread Chris

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

2008-10-15 Thread Jack van Zanen
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

2008-10-15 Thread Piyush Kumar
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

2008-10-15 Thread Chris

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

2008-10-15 Thread Chris

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

2007-05-14 Thread Dwight Altman
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

2005-11-19 Thread Bastien Koert

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!

2005-10-11 Thread Bastien Koert
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

2005-04-13 Thread Bastien Koert
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...

2005-03-24 Thread J. Connolly
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...

2005-03-24 Thread Bastien Koert
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...

2005-03-24 Thread NIPP, SCOTT V \(SBCSI\)
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...

2005-03-23 Thread Bastien Koert
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

2005-03-19 Thread Bastien Koert
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

2005-03-19 Thread Larry E . Ullman
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

2004-08-13 Thread Torsten Roehr
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

2004-08-13 Thread Justin Patrin
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...

2004-07-06 Thread Jason Wong
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

2004-03-17 Thread Bruno Ferreira
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

2004-03-17 Thread Matt Matijevich
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

2004-03-17 Thread Adam Voigt
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

2004-03-17 Thread Ryan Jameson (USA)
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?

2004-03-01 Thread Jason Wong
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?

2004-03-01 Thread Tristan . Pretty
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

2003-10-18 Thread Steve Davies
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

2003-10-18 Thread Sean Smitz
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

2003-10-17 Thread Matthew Moldvan, Jr.
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...

2003-09-21 Thread Fedde van Feggelen

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

2003-09-21 Thread John W. Holmes
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

2003-08-27 Thread Jennifer Goodie
 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

2003-08-27 Thread Andy Green
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

2003-08-27 Thread CPT John W. Holmes
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

2003-08-27 Thread jeffrey_n_Dyke

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!

2003-03-12 Thread Mark
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!

2003-03-12 Thread Griffiths, Daniel
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

2003-01-23 Thread Frank Peavy
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

2003-01-23 Thread Ignatius Reilly
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

2003-01-23 Thread John W. Holmes
 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

2002-09-04 Thread Andrey Hristov

Choices
1) Use InnoDB
2) If you cannot use innodb than you can write your code in the way you add
in an array
queries that will rollback the change :

if you do
$r1 = mysql_query('insert into some_table (k1,k2) values (4,2);');
$r1 = mysql_query('insert into some_table (k1,k2) values (5,3);');

you must have in the rollback array this
$rollback_ar = array('DELETE FROM some_table WHERE k1=4 and k2=2;',
'DELETE FROM some_table WHERE k1=5 and k2=3');

Hope this is clear.

Best regards
Andrey Hristov


- Original Message -
From: :B nerdy [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, September 04, 2002 3:04 PM
Subject: [PHP-DB] [mysql] query atomicity


 how would i go about making a few queries into a transaction - that is, if
 one of them fails, the previous queries get rolled back...

 cheers




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



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




Re: [PHP-DB] [mysql] query atomicity

2002-09-04 Thread :B nerdy

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

2002-06-21 Thread William Fong

I would suggest using ' instead of  for SQL queries... That's what I use...
so much easier.

$query = UPDATE articles2 SET title='$title', brief='$brief',
doclink='$doclink' WHERE article_id='$id';

Then I would print the query:

print( $query\n );

Then if it still looks right, try manually typing it in a CLI interface.

-w



--
William Fong - [EMAIL PROTECTED]
Phone: 626.968.6424 x210  |  Fax: 626.968.6877
Wireless #: 714.315.8954|  Wireless E-mail:


- Original Message -
From: Martin Clifford [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, June 20, 2002 10:03 AM
Subject: [PHP-DB] MySQL Query Acting Up


Howdy all,

If someone call tell me why this MySQL query is not working, I'd be VERY
thankful.  I don't see a damn thing wrong with it, but I could have been
looking at it too long.

Here is the query:

$query = UPDATE articles2 SET title=\$title, brief=\$brief\,
doclink=\$doclink\ WHERE article_id=\$id\;
$result = mysql_query($query, $connect)
 or die(mysql_error());

No errors are produced, yet the query doesn't do anything.  Nothing in the
row changes.  Any help would be appreciated!

Martin


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





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




Re: [PHP-DB] MySQL Query

2002-05-03 Thread Alnisa Allgood

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

2001-12-14 Thread Miles Thompson

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

2001-10-26 Thread Raquel Rice

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

2001-10-25 Thread Russ Michell

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

2001-10-25 Thread Rick Emery


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

2001-10-25 Thread Tamas Arpad

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

2001-10-25 Thread Rick Emery

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

2001-10-25 Thread Niklas Lampén

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

2001-10-25 Thread Rick Emery

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

2001-10-25 Thread Arpad Tamas

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

2001-09-21 Thread Rick Emery

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

2001-09-20 Thread Steve Cayford

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

2001-07-26 Thread Paul Burney

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]