read from two mysql tables into one form

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
inosent1
Forum Commoner
Posts: 97
Joined: Wed Jan 28, 2009 12:18 pm

read from two mysql tables into one form

Post 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
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: read from two mysql tables into one form

Post 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
inosent1
Forum Commoner
Posts: 97
Joined: Wed Jan 28, 2009 12:18 pm

Re: read from two mysql tables into one form

Post 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))
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: read from two mysql tables into one form

Post 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().
inosent1
Forum Commoner
Posts: 97
Joined: Wed Jan 28, 2009 12:18 pm

Re: read from two mysql tables into one form

Post 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
Post Reply