Jump to content

PHP Code in a MYSQL Query


Recommended Posts

  • Administrators

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());

Link to post
Share on other sites
  • Administrators

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());

Link to post
Share on other sites

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.

Link to post
Share on other sites
  • Administrators

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 . "'";

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...