[SOLVED] Integrating data from MySQL into email

[dubz99]

Hello all!

 

I am currently attempting to create a custom order email notification in osCommerce.  I am doing well so far but have now hit a wall!

 

I am attempting to add data from my products_stock table specifically the products_code for multiple products ordered.

 

Please be gentle as I am a novice!

 

I have asked the same question in the OSC forum but had no luck, the link to that thread is http://forums.oscommerce.com/index.php?showtopic=309242

 

Thanks!!

dubz

[dubz99]

Just noticed I placed this in the wrong category!  Please feel free to move.

 

Thanks

dubz

[dbo]

It really shouldn't be anymore than running a query against the database... storing the values returned from the result set and then concatenating them into a string which you pass to the mail function.

 

Pseudo code:

 

$query = "SELECT * FROM products WHERE ID = 5";
$result = mysql_query($query);

if( mysql_num_rows($result) > 0 )
{
   $row = mysql_fetch_assoc($result);

   $name = $row['Name'];
   $price = $row['Price'];

   $message = "Thanks for your interest in $name for $price.";

   mail($to, "Online inquiry", $message, $from);   
}
else
{
   //error
}

 

This is again not functioning code... just proof of concept stuff, but that should be about all you need to do assuming that I understand your question right.

[dubz99]

Thanks for the reply

 

Does this look right minus the email message>?

 

$stock_id_query = "SELECT products_code FROM " . TABLE_PRODUCTS_STOCK . " WHERE ID = '" . tep_get_prid($p['id'];
	$stock_id_result = mysql_query($stock_id_query);

	if( mysql_num_rows($stock_id_result) > 0 )
	{
  		 $row = mysql_fetch_assoc($stock_id_result);

   			$stock_id_display = $row['products_code'];

 

Dubz

[dbo]

Conceptually you're on the right track, however there is at least one syntax error I see:

 

tep_get_prid($p['id']

 

should probably be:

 

tep_get_prid($p['id'])

[dubz99]

Great thanks I'll give it a try

 

|dubz|

[dubz99]

dbo,

 

Well maybe it may help me and others by commenting what each line does in your previous post. 

 

After adding the below code, with your correction, all went good except the email did not contain any data it was supposed to from the above.

 

Thanks again for your time!

 

|dubz|

[dubz99]

Somehow I missed it the first time but now I am receiving an error, even though the email goes through.

 

Problem (default): undefined

<br />
<b> Warning</b>: mysql_num_rows(): supplied argument is not a valid MySQL result resourse in
<b>/home/public_html/checkout.php</> on line
<b> 942 </> <br />

 

Below is line 942 the error is referring to

if( mysql_num_rows($stock_id_result) > 0 )

[dbo]

There is apparently something wrong with your query. The result you are passing to mysql_num_rows is not a valid result set, thus its throwing an error message.

 

You'll need to post all of your code, and I'd suggest adding a die statement after your query for debugging purposes.

 

$result = mysql_query($query) or die("ERROR: " . mysql_error());

[dubz99]

Here are the lines I inserted, I added some items as I was trying to troubleshoot.

 

Thanks

|dubz|

 

					
	$stock_id_query = "SELECT products_code FROM " . TABLE_PRODUCTS_STOCK . " WHERE " . products_id . " = " . tep_get_prid($p['id']);
	$stock_id_result = mysql_query($stock_id_query) or die("<br />". mysql_error());

if( mysql_num_rows($stock_id_result) > 0 )  //doesn't like this line
	{
		 $row = mysql_fetch_assoc($stock_id_result);

   			$stock_id_display = $row['products_code'];
   			
}
  		 
else
{
   print (mysql_error());
}

[dbo]

So what's the error message that you are getting? It clearly doesn't like the query...

 

you can put echo $query; exit; after your create the query string to validate that it does in fact create the query string you think it does. You can then paste this query into a tool such as phpMyAdmin to see the error message if you can't seem to get the error using mysql_error()

[dubz99]

Well I think I am losing it, with the code posted above I am now not receiving any error but the data is still not being placed into the e-mail.

 

Any suggestions

 

|dubz|

[dubz99]

OK,  I've been staring at this code too long!

 

You were right! And it is now working flawlessly!

 

The field for the product I was asking for was blank!  Hence the e-mail came through blank!

 

Thanks so much for your help!

 

|dubz|