Page 1 of 1

read from two mysql tables into one form

Posted: Thu Mar 29, 2012 8:51 pm
by inosent1
i have a long form with several fields for submission.

i used this code for a long time and it worked great -- if all i needed was data from one table to be fed into the form:

Code: Select all

<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$term = $_POST['term'];

$sql = mysql_query("select * from ldata where id = '$term'");
while ($row = mysql_fetch_array($sql))

{

?>
<table><tr><td>
<input type="text"  value="<?php echo $row['b_fname'];?>">

<?
}
?>
that is the very simple version. in between the '{' and the '}' are hundreds of fields, etc.

but underneath 'b_fname' is another section of the form, and i need it to draw from 'cdata' instead of 'ldata'. and then once it all fills in right, changes made, etc, i submit the entire thing back to update the mysql DB

i tried doing:

Code: Select all

<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$term = $_POST['term'];

$sql = mysql_query("select * from ldata where id = '$term'");
while ($row = mysql_fetch_array($sql))
$sql2 = mysql_query("select * from cdata where id = '$term'");
while ($row2 = mysql_fetch_array($sql2))
{
but that didn't work

any ideas greatly appreciated

Re: read from two mysql tables into one form

Posted: Thu Mar 29, 2012 9:06 pm
by Celauran
Use a JOIN

Code: Select all

SELECT a.some_field, b.some_other_field
FROM first_table AS a
JOIN second_table AS b ON b.id = a.id
WHERE some_condition
etc

Re: read from two mysql tables into one form

Posted: Thu Mar 29, 2012 10:54 pm
by inosent1

Code: Select all

$sql = mysql_query("SELECT * FROM ldata AS a JOIN cdata AS b ON b.file_id = a.id WHERE id = '$term'");
while ($row = mysql_fetch_array($sql))
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home/inovapro/public_html/data/edit_a.php on line 42

Code: Select all


$sql = mysql_query("SELECT * 
FROM ldata AS a 
JOIN sec_data AS b 
ON b.file_id = a.file_id 
[LINE 42>>] WHERE file_id = '$term2'");
while ($row = mysql_fetch_array($sql))

Re: read from two mysql tables into one form

Posted: Fri Mar 30, 2012 5:53 am
by Celauran
inosent1 wrote:

Code: Select all

$sql = mysql_query("SELECT * FROM ldata AS a JOIN cdata AS b ON b.file_id = a.id WHERE id = '$term'");
while ($row = mysql_fetch_array($sql))
First, SELECT * is always wrong. Using it in a JOIN is even worse. Figure out which columns you need and select only those. Your query is currently returning false, so I'd start by checking the output of mysql_error().

Re: read from two mysql tables into one form

Posted: Fri Mar 30, 2012 9:13 am
by inosent1
this solved the problem (placing this code just above the lower half of the form where the data was to be pulled from the second table)

Code: Select all

<?
}
$sql = mysql_query("select * from sec_data where file_id = '$term2'");
while ($row = mysql_fetch_array($sql))
{
?>

iow, closed the first query above, and opened up a new one on the bottom