Page 1 of 1

Fill my mail with database-variables ?!!!?

Posted: Thu Feb 05, 2004 1:39 pm
by Braindead)1
My problem is as follows:

I can't get my mail-function to work properly.
Here is my code and my question:

Code: Select all

$toaddress = 'tome@me.com';
$subject = 'my subject';

$db = mysql_connect('localhost', 'user', '') or die(mysql_error()); 
mysql_select_db('database') or die(mysql_error()); 
$query = "SELECT * FROM orders ORDER BY orderid DESC LIMIT 1";
$result = mysql_query($query) or die("SELECT Error: ".mysql_error());
$num_results = mysql_num_rows($result);

for ($i=0; $i < $num_results; $i++)
{
$row = mysql_fetch_array($result);

mysql_close($db);
}
So basically I have info from my 'orders' table which I want to insert into my mail.

The following code:

Code: Select all

mail ($toaddress, $subject, $row);
doesn't seem to do the trick.
What am I missing here???

Posted: Thu Feb 05, 2004 1:49 pm
by Illusionist
first off, by "insert into my mail" i'm guessing that your trying to send it to yourself... If thats wrong, then be a little but more specific with your questions!

and 2)

Code: Select all

for ($i=0; $i < $num_results; $i++) 
{ 
$row = mysql_fetch_array($result); 

mysql_close($db); 
}
Do you even get any result when doing this? Because i really dont think you will... 1 reason is because your limiting only 1 result in your SELECT statement... so num_rows will never be greater then 1... correct me if im wrong! instead try doing something like:

Code: Select all

while ($row = mysql_fetch_array($result)) {
    $data = $row['FEILD_NAME'];
}
mysql_close($db);
But replace FIELD_NAME with what ever field name your wanting...

Posted: Thu Feb 05, 2004 2:43 pm
by Braindead)1
That last tip of yours was a good one.
I intentially left the space under

Code: Select all

$row = mysql_fetch_array($result);
open, for extra info to come.
I just didn't know what to put there.
So your extra input was most welcome. It worked like a dream.

Now step 1 has been taken, and it's time to move on.
I am really trying to get info (not all !!) from 2 tables into 1 mail.
Both tables have the identifier 'orderid' which gives me a reference.

My question now is:
How can I fulfill this ???

To start with my 'SELECT'-statement:
The orderid is indeed limited to 1, but there are a number of fields inthere.
Therefore:

Code: Select all

$query = "SELECT * FROM orders ORDER BY orderid DESC LIMIT 1";
can produce an output of more than 1 (this field has info like: mail, address, city, amount etc)

Another table is called 'order_items' where article_price, article_number and quantity are stored in.

I have tried the command:

Code: Select all

$query = "SELECT * FROM orders,order_items WHERE orderid = '$orderid' ORDER BY orderid DESC LIMIT 1";
but the result is a fault-code which says:

SELECT Error: Column: 'orderid' in where clause is ambiguous

I have tried other ways, with 'orderid' as my main parameter, but the fault has probably something to do with the 2 tables in my SELECT parameter.


Am I correct, or is my way of thinking completely wrong???

Posted: Thu Feb 05, 2004 4:54 pm
by Illusionist
uhm.. try making it SELECT *,*
If taht doesn't work, make sure your passing something through $orderid

Posted: Fri Feb 06, 2004 9:07 am
by Braindead)1
Nope, that didn't work.
I am a little bit further though....

Please, take a look at the following code:

Code: Select all

$db = mysql_connect('localhost', 'root', '') or die(mysql_error()); 
mysql_select_db('dbname') or die(mysql_error()); 
$query = "SELECT orders.amount, order_items.artnr FROM orders, order_items WHERE orders.orderid = order_items.orderid";
$result = mysql_query($query) or die("SELECT Error: ".mysql_error());
$num_results = mysql_num_rows($result);

while ($row = mysql_fetch_array($result)) {
    echo $row['amount']."<br />";
    echo $row['artnr']."<br />";
I know the last two lines are used to visualize it on the screen, but I can easily put it in my mail with the commands:

Code: Select all

$data1 = $row['amount']; 
$data2 = $row['artnr'];

$total = 'Amount =  '.$data1."\n" 
	.'Article number = '.$data2."\n";

mail ($toaddress, $subject, $total);
Even though I can now subtrackt info from multiple tables, I still don't get the desired result.
I only want the info with the highest orderid-number, and not (like I have now) all matching orderid numbers and the 'amount' and 'artnr' values that come with these orderid's.

Can anyone help me?? :? :?

Posted: Fri Feb 06, 2004 9:39 am
by Illusionist
add

Code: Select all

ORDER BY orders.orderid DESC LIMIT 1
to you SQL SELECT statement and see if that works...

Posted: Fri Feb 06, 2004 9:54 am
by Braindead)1
Your code did the trick.
I can now see only 1 output. Exactly what I wanted.
Your solution was so simple...
how come I didn't see that??? :oops:

Anyway,

tnx

Posted: Fri Feb 06, 2004 11:50 am
by Illusionist
:) anytime