PHP Code in a MYSQL Query

[Nathan]

I'm trying to figure out how to use this code, but it's not working right.

 

This query works fine:

$var1= $_POST['user'];
$var2 = $_POST['order'];
//Connect to the Database
$host = localhost;
$username = user;
$password = pass;
$connect = mysql_connect($host,$username,$password);
//Select the Database
$db = 'devwp';
mysql_select_db($db);
//Query the Needed Data
$query = 'select distinct pm.meta_value
from wp_shopperpress_orders o
inner join wp_posts p on left(o.order_items, instr(o.order_items, "x")-1)  = p.id
inner join wp_postmeta pm on p.id = pm.post_id';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

 

I want to modify it to have a where clause in the query, but the code I'm trying to do fails. I'm trying to have $var2 filter in the where clause.

$var1= $_POST['user'];
$var2 = $_POST['order'];
//Connect to the Database
$host = localhost;
$username = user;
$password = pass;
$connect = mysql_connect($host,$username,$password);
//Select the Database
$db = 'devwp';
mysql_select_db($db);
//Query the Needed Data
$query = 'select distinct pm.meta_value
from wp_shopperpress_orders o
inner join wp_posts p on left(o.order_items, instr(o.order_items, "x")-1)  = p.id
inner join wp_postmeta pm on p.id = pm.post_id where o.order_id =  ' . $var2;
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

[Nathan]

Well finished it up now. The reason it wasn't pulling was because $var2 was a string not a number so I needed quotes around it.

 

$var1 = $_POST['user'];
$var2 = $_POST['order'];

//Connect to the Database
$host = localhost;
$username = user;
$password = pass;
$connect = mysql_connect($host,$username,$password);

//Select the Database
$db = 'devwp';
mysql_select_db($db);

//Query the Needed Data
$query = 'select meta_value
from wp_orderdata o
inner join wp_posts p on left(o.order_items, instr(o.order_items, "x")-1)  = p.id
inner join wp_postmeta pm on p.id = pm.post_id where meta_key = "qty" and o.order_id =' . "'" . $var2 . "'";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

[haye55987]

Nathan, can you pull out the code syntax highlighting and show where the error is? Also, do you receive an error log when you were running the initial process?

 

I don't do much in MySQL but I'd like to learn as much if you can post the reason you received the error and where exactly in the code it error'ed.

[Nathan]

The issue was on the 2nd to last line. This is the original code:

inner join wp_postmeta pm on p.id = pm.post_id where o.order_id =  ' . $var2;

 

This is how I fixed it. Since the variable $var2 is pulling a text string from the database it has to be enclosed in quotes.

inner join wp_postmeta pm on p.id = pm.post_id where meta_key = "qty" and o.order_id =' . "'" . $var2 . "'";