10 Replies - 1441 Views - Last Post: 18 September 2012 - 06:28 AM Rate Topic: -----

#1 dogman73  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 17-September 12

PHP MySql Query Failing

Posted 17 September 2012 - 02:23 PM

First this is going into an app and is a snippit to be used by that app. Hence mysql instead of mysqli. The variable $mpn in the first query comes from $mpn = ($P[0]); at the top. It is in a feed and not in the database as a column. $sku in the second query comes from the result of the first query and is not in the database as a column. The app loops this code for each line of the feed.

The below fails to achieve the desired effect of providing the last argument even though I know it for sure should (I set it up so it would if it worked). Since it is in an app it's the only way I have to debug. Can somebody help?

$mpn = ($P[0]);
$qty = ($P[14]);

$mysql_connection = mysql_connect('localhost', 'user', 'pass' );
if (!$mysql_connection) {
die('Could not connect to db: ' . mysql_error());
}

mysql_select_db('db', $mysql_connection);

$tmp1 = mysql_query("SELECT productcode FROM table1 WHERE mpn != '' && mpn = '$mpn'")or die(mysql_error());
$row1 = mysql_fetch_array($tmp1, MYSQL_BOTH);
$sku = $row1[0];

$tmp2 = mysql_query("SELECT avail FROM table1 WHERE productcode = '$sku'")or die(mysql_error());
$row2 = mysql_fetch_array($tmp2, MYSQL_NUM);
$qty2 = $row2[0];

if ($qty2 <=0 && $qty >0) { 
return($sku);
}
else {
return("XXX-99999");
}


Is This A Good Question/Topic? 0
  • +

Replies To: PHP MySql Query Failing

#2 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 782
  • View blog
  • Posts: 1,662
  • Joined: 30-January 09

Re: PHP MySql Query Failing

Posted 17 September 2012 - 02:43 PM

Lines 1 and 2 don't need the brackets around the variable, this will suffice:
$mpn = $P[0];
$qty = $P[14];



Your SQL syntax on line 11 is wrong. Also, I can't see why you'd have a != and an = in the same clause group, and the latter implies the former. Another thing, && is not valid SQL, you need to use AND. Try this instead:
SELECT productcode FROM table1 WHERE mpn = '$mpn'



Is there a reason why you're fetching as MYSQL_BOTH on line 12 and as MYSQL_NUM on line 16? Do you know what the difference is between the two?

The returns on lines 20 and 23 don't require brackets around them as return is a language construct, not a function. Also, unless this code snippet is part of a function, you're going to have problems, as return in a main block halts script execution.

This post has been edited by e_i_pi: 17 September 2012 - 02:58 PM

Was This Post Helpful? 1
  • +
  • -

#3 dogman73  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 17-September 12

Re: PHP MySql Query Failing

Posted 17 September 2012 - 03:27 PM

$tmp1 = mysql_query("SELECT productcode FROM table1 WHERE mpn != '' && mpn = '$mpn'")
The != is to prevent the query from finding 2 matches and only finding the desired one as there are values only in those that I want.

The Line 12 MYSQL_BOTH is because there are numeric, textual and mixed values when the app loops through the feed. Line 16 is always numeric.

The snippit is part of a function I am sure or they would not allow it's use.
Was This Post Helpful? 0
  • +
  • -

#4 dogman73  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 17-September 12

Re: PHP MySql Query Failing

Posted 17 September 2012 - 03:40 PM

Also return() is their requirement for output from the app.
Was This Post Helpful? 0
  • +
  • -

#5 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 782
  • View blog
  • Posts: 1,662
  • Joined: 30-January 09

Re: PHP MySql Query Failing

Posted 17 September 2012 - 03:43 PM

View Postdogman73, on 18 September 2012 - 09:27 AM, said:

$tmp1 = mysql_query("SELECT productcode FROM table1 WHERE mpn != '' && mpn = '$mpn'")
The != is to prevent the query from finding 2 matches and only finding the desired one as there are values only in those that I want.

But if mpn = '$mpn', then it follows that mpn != '', therefore you don't need to state that explicitly.

It still remains that your syntax is incorrect, if you use the code I posted above, that should fix your issue.
Was This Post Helpful? 1
  • +
  • -

#6 dogman73  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 17-September 12

Re: PHP MySql Query Failing

Posted 17 September 2012 - 04:02 PM

I corrected the syntax. There are 2 instances, where there is a match to $mpn from the feed and mpn = '$mpn' returns TRUE. I only want the match that has a value in mpn.
Was This Post Helpful? 0
  • +
  • -

#7 dogman73  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 17-September 12

Re: PHP MySql Query Failing

Posted 17 September 2012 - 04:33 PM

I replaced && with AND, still no success. I also tried
$tmp1 = mysql_query("SELECT productcode FROM xcart_products WHERE mpn != '' AND mpn = '".$mpn."'")or die(mysql_error());
$row1 = mysql_fetch_array($tmp1, MYSQL_BOTH);
$sku = $row1[0];

$tmp2 = mysql_query("SELECT avail FROM xcart_products WHERE productcode = '".$sku."'")or die(mysql_error());
$row2 = mysql_fetch_array($tmp2, MYSQL_NUM);
$qty2 = $row2[0];
Did not work.
Was This Post Helpful? 0
  • +
  • -

#8 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 782
  • View blog
  • Posts: 1,662
  • Joined: 30-January 09

Re: PHP MySql Query Failing

Posted 17 September 2012 - 04:45 PM

Have you tried what I originally suggested?
SELECT productcode FROM table1 WHERE mpn = '$mpn'


You don't need the != part, and in fact that is the wrong syntax for "not equal" in SQL.
Was This Post Helpful? 1
  • +
  • -

#9 dogman73  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 17-September 12

Re: PHP MySql Query Failing

Posted 17 September 2012 - 05:15 PM

I tried

$tmp1 = mysql_query("SELECT productcode FROM table1 WHERE mpn = '$mpn'")or die(mysql_error());
$row1 = mysql_fetch_array($tmp1, MYSQL_BOTH);
$sku = $row1[0];

$tmp2 = mysql_query("SELECT avail FROM table1 WHERE productcode = '$sku'")or die(mysql_error());
$row2 = mysql_fetch_array($tmp2, MYSQL_NUM);
$qty2 = $row2[0];


Still did not work. Since $mpn and $sku are not mysql variables rather they are PHP variables. Could that be a problem?
Was This Post Helpful? 0
  • +
  • -

#10 dogman73  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 17-September 12

Re: PHP MySql Query Failing

Posted 17 September 2012 - 05:43 PM

The below worked for those needing it.

$mpn = ($P[5]);
$qty = ($P[14]);

$mysql_connection = mysql_connect('localhost', 'user', 'pass' );
if (!$mysql_connection) {
die('Could not connect to db: ' . mysql_error());
}

mysql_select_db('db', $mysql_connection);

$tmp1 = mysql_query("SELECT productcode FROM table1 WHERE mpn = '$mpn'")or die(mysql_error());
$row1 = mysql_fetch_array($tmp1, MYSQL_BOTH);
$sku = $row1[0];

$tmp2 = mysql_query("SELECT avail FROM table1 WHERE productcode = '$sku'")or die(mysql_error());
$row2 = mysql_fetch_array($tmp2, MYSQL_NUM);
$qty2 = $row2[0];

if ($qty2 <=0 && $qty >0) {
return($sku);
}
else {
return("XXX-99999");
}
Thank you e_i_pi
Was This Post Helpful? 0
  • +
  • -

#11 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3635
  • View blog
  • Posts: 5,756
  • Joined: 08-June 10

Re: PHP MySql Query Failing

Posted 18 September 2012 - 06:28 AM

View Poste_i_pi, on 17 September 2012 - 09:43 PM, said:

Another thing, && is not valid SQL, you need to use AND.

View Poste_i_pi, on 17 September 2012 - 11:45 PM, said:

You don't need the != part, and in fact that is the wrong syntax for "not equal" in SQL.

Where MySQL is concerned, at least, you are wrong on both counts. It treats && and AND equally. It also treats != and <> equally. Its treatment of && is not widely supported by other database servers, but != is supported by most, if not all, major database servers.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1