Output SQL query into Variable?

[leoric80]

Hi

 

I am completely new to PHP (and this forum) so be gentle

 

I have a html web form that posts to a PHP page, the posts are then stored as a variable and then written to a database as per the following code.

<?php

include "database.php";
$school_id = $_POST['school_id'];
// Data for text message
$student_id = $_POST['student_id'];
$senderno = $_POST['senderno'];
$destno = $_POST['destno'];
$message = $_POST['message'];
$message = urlencode($message);


mysql_query("INSERT INTO ".$school_id."_outbox (senderno, destno, message, student_id)
VALUES ('$senderno', '$destno', '$message', '$student_id')");


?>

What I would like to do is the following...

 

If the value of $destno is equal to "WholeSchool" I would like to run a the SQL query "select 1_contacts.p1contact_no from 1_contacts" and store the results of each row into a variable called $WholeSchool for use into the following query..

mysql_query("INSERT INTO ".$school_id."_outbox (senderno, destno, message, student_id)
VALUES ('$senderno', '$WholeSchool', '$message', '$student_id')");

This probably doesn't make a great deal of sense but if anyone could help me out with this then I would be hugely grateful.

 

Thanks!

[leoric80]

Just to add...

 

I guess it would need to do an insert query for each row generated by the select query?

 

Thanks

[ginerjm]

Actually if you look up insert query in the MySQL website  (just google MySQL insert) you will see an example of this exact thing.

 

google is your friend - use it.

[leoric80]

Actually if you look up insert query in the MySQL website  (just google MySQL insert) you will see an example of this exact thing.

 

google is your friend - use it.

 

I have googled it but don't seem to find exactly what I am looking for, could you provide me with a link perhaps? I am not having any trouble inserting the data, the trouble I am having is outputting the result of the select query into a variable that I can use in my insert query.

 

Thanks for your help

[Quostin]

Not sure if this is exactly what you want... 

if ($destno == "WholeSchool")
         {
         $WholeSchool1 = mysql_query("Select * FROM 1_contacts");
         while ($WholeSchool2 = mysql_fetch_assoc($WholeSchool1))
            {
            $WholeSchool = "$WholeSchool$WholeSchool2[1_contacts]: $WholeSchool2[p1contact_no] , ";
            }
         $WholeSchool = substr($string, 0, -2);

         mysql_query("INSERT INTO ".$school_id."_outbox (senderno, destno, message, student_id)
         VALUES ('$senderno', '$WholeSchool', '$message', '$student_id')");
         }
else
         {
         mysql_query("INSERT INTO ".$school_id."_outbox (senderno, destno, message, student_id)
         VALUES ('$senderno', '$destno', '$message', '$student_id')");
         }

It is kind of corny, but should work. It will separate each data with a comma and at the end of it all, it deletes the last space and comma to keep it clean. I didn't try inserting it into a database, but echo $WholeSchool gives me the right results.

[Quostin]

Oh dang, should have proofread it all... change the $string to $WholeSchool. if 1_contacts.p1contact_no is the whole row name then just do 

$WholeSchool = "$WholeSchool$WholeSchool2[1_contacts.p1contact_no] , ";

[Barand]

Is this what you are trying to do?

mysql_query("INSERT INTO ".$school_id."_outbox (senderno, destno, message, student_id)
SELECT '$senderno', p1contact, '$message', '$student_id' FROM 1_contacts");

[leoric80]

 

Oh dang, should have proofread it all... change the $string to $WholeSchool. if 1_contacts.p1contact_no is the whole row name then just do 

$WholeSchool = "$WholeSchool$WholeSchool2[1_contacts.p1contact_no] , ";

Quostin Thank you so much for your help as it looks to be exactly what I need, could you please post this along with the correction you made there after proof reading and I will give it a go! Thank you again

[leoric80]

Hi I get the following error message when i test this code..any ideas?

 

php -l outbox.php 

PHP Parse error:  syntax error, unexpected T_STRING, expecting ']' in outbox.php on line 23

Errors parsing outbox.php

 

if ($destno == "WholeSchool")
         {
         $WholeSchool1 = mysql_query("Select * FROM 1_contacts");
         while ($WholeSchool2 = mysql_fetch_assoc($WholeSchool1))
            {
            $WholeSchool = "$WholeSchool$WholeSchool2[1_contacts]: $WholeSchool2[p1contact_no] , ";
            }
         $WholeSchool = substr($WholeSchool, 0, -2);

         mysql_query("INSERT INTO ".$school_id."_outbox (senderno, destno, message, student_id)
         VALUES ('$senderno', '$WholeSchool', '$message', '$student_id')");
         }
else
         {
         mysql_query("INSERT INTO ".$school_id."_outbox (senderno, destno, message, student_id)
         VALUES ('$senderno', '$destno', '$message', '$student_id')");
         }

Edited June 18, 2013 by leoric80

[leoric80]

So I got this working as desired using the following code, thank you again for all your help!

if ($destno == "WholeSchool")
         {

$result = mysql_query("Select * FROM 1_contacts");
        while ($row = mysql_fetch_assoc($result)) {
$everyone = $row["p1contact_no"];

 mysql_query("INSERT INTO ".$school_id."_outbox (senderno, destno, message, student_id)
 VALUES ('$senderno', '$everyone', '$message', '$student_id')");

}

}
else
         {
         mysql_query("INSERT INTO ".$school_id."_outbox (senderno, destno, message, student_id)
         VALUES ('$senderno', '$destno', '$message', '$student_id')");
         }

[ginerjm]

For future reading here is the link you asked for:

 

http://dev.mysql.com/doc/refman/5.5/en/insert-select.html

[Barand]

you should not run queries inside loops, it kills performance. The efficient solution was the one I gave you above http://forums.phpfreaks.com/topic/279279-output-sql-query-into-variable/?do=findComment&comment=1436579

Edited June 18, 2013 by Barand