[RESOLVED] Issue with update form

snoopgreen

Hello everyone

I was hoping someone could give me some advice / help with an problem i have with my update form.

well first thing i was wondering if you can update many tables at one time
coz im trying to update 3 tables can this be done with 1 update statement?

and also im trying to output the revelant data on the screen so the user can view what they are updating
but when i try to output morethan one tables data nothing is appearing
can anyone help with this issue also?
the code im using to output the data is below:

$result = mysql_query("SELECT * FROM external_examiners, prog_externals", $db);
While($rec = mysql_fetch_array($result)) {
	echo("External ID: " . $rec["external_id"] . "<br>\n");
	echo("External Name: " . $rec["name"] . "<br>\n");
	echo("Contact Details: " . $rec["contact_details"] . "<br>\n");
	echo("Email Address: " . $rec["email_address"] . "<br>\n");
	echo("Telephone Number: " . $rec["tele_num"] . "<br>\n");
	echo("Present Organisation: " . $rec["pres_organisation"] . "<br>\n");
	echo("Start Date: " . $rec["start_date"] . "<br>\n");
	echo("Finish Date: " . $rec["finish_date"] . "<br><br>\n");

with this code when i just select all from external_examiners the data appears but the last 2 echo statements come from the prog_externals table
but when ive got this code nothing appears
not even any errors
any help would be greatly apprecaited
snoopgreen

rincewind456

You will need to use a join in that query, because by selecting all from both tables in that way you are going to create a huge amount of spurious data, for instance if you have 2 tables like :

table1
id, name email

table2
id, phone, birthday

with just 4 records, you would get the following results form the query "SELECT * FROM table1, table2"

1	fred		fred@home	1	5551234	10.1.04
2	Joe		Joe@home	1	5551234	10.1.04
3	Bert		bert@home	1	5551234	10.1.04
4	Lucy		lucy@home	1	5551234	10.1.04
1	fred		fred@home	2	4444567	12.10.99
2	Joe		Joe@home	2	4444567	12.10.99
3	Bert		bert@home	2	4444567	12.10.99
4	Lucy		lucy@home	2	4444567	12.10.99
1	fred		fred@home	3	3337896	14.05.78
2	Joe		Joe@home	3	3337896	14.05.78
3	Bert		bert@home	3	3337896	14.05.78
4	Lucy		lucy@home	3	3337896	14.05.78
1	fred		fred@home	4	2229631	25.12.85
2	Joe		Joe@home	4	2229631	25.12.85
3	Bert		bert@home	4	2229631	25.12.85
4	Lucy		lucy@home	4	2229631	25.12.85

whereas with a join like "SELECT * table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id" would return the following:


1	fred		fred@home	1	5551234	10.1.04
2	Joe		Joe@home	2	4444567	12.10.99
3	Bert		bert@home	3	3337896	14.05.78
4	Lucy		lucy@home	4	2229631	25.12.85

as you can see if you have a 100 rows you will get 10,000 results with your query.

Also you should append

or die(mysql_error())

to the end of all your queries, otherwise you won't get any error messages.

snoopgreen

Hello thanks for your help

but now im getting the error below:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/ca5cgr/public_html/PHPBookExamples/updateExtExamPage.php on line 22
Which means my SQL statement isnt right doesnt it?

the code im using is below:

$result = mysql_query("SELECT * FROM external_examiners e1 INNER JOIN prog_externals p1 ON e1.externals_id = p1.external_id", $db);
While($rec = mysql_fetch_array($result)) {
	echo("External ID: " . $rec["external_id"] . "<br>\n");
	echo("External Name: " . $rec["name"] . "<br>\n");
	echo("Contact Details: " . $rec["contact_details"] . "<br>\n");
	echo("Email Address: " . $rec["email_address"] . "<br>\n");
	echo("Telephone Number: " . $rec["tele_num"] . "<br>\n");
	echo("Present Organisation: " . $rec["pres_organisation"] . "<br>\n");
	echo("Start Date: " . $rec["start_date"] . "<br>\n");
	echo("Finish Date: " . $rec["finish_date"] . "<br><br>\n");

any more help would be greatly appreicated
thanks again
snoopgreen

rincewind456

It usually means that the query result is empty.

If you have phpMyadmin or the like, test the query in that to see that you're getting the results you want, then transfer it to the PHP script.
At least then you will know that the query is working.

snoopgreen

oh thanks for the help again

ive now got that part working

but now i need to output another tables data
how would i go about joining this to the SQL statement?

would i just use and AND statement
or would i use another JOIN statement?

$result = mysql_query("SELECT * FROM external_examiners INNER JOIN prog_externals ON external_examiners.external_id = prog_externals.external_id", $db);

i have a table called external_visits which has the external_examiners.external_id as a foreign key in it
so could you please tell me how i would incorporate thais please

thanks for the help / advice
snoopgreen

rincewind456

You'd need a join again, otherwise you'll have the same problems. Something like

$result = mysql_query("SELECT * FROM external_examiners e
 INNER JOIN prog_externals p ON e.external_id = p.external_id
 INNER JOIN external_visits v ON e.external_id = v.external_id", $db);

snoopgreen

thanks alot for the fast reply

im sorry to say but could i ask you another question please?

im trying to update tables and i was wondering if you can update more than 1 table using 1 UPDATE statement
coz im trying to update 2 at present and its not working

the code im using is

$result = mysql_query("UPDATE external_examiners, prog_externals SET external_examiners.name='$ExtName', external_examiners.contact_details='$ContactDetails',
				external_examiners.email_address='$Email', external_examiners.tele_num='$Tele', external_examiners.pres_organisation='$presOrgan', prog_externals.start_date='$start', prog_externals.finish_date='$finish'
			WHERE external_id = '" . $external_id ."'",$db);
			echo("<b><br><br>Record Updated</br></br></b>");

Is this allowed or do i have to do 2 UPDATE statements or something a like?

thanks for all your help
snoopgreen

snoopgreen

hello thanks for your help

ive sorted my last post ive ben able to update 2 tables using 1 update statement

but what ive noticed is if i leave some of the fields blank a blank value gets entered into the database taking away the value from whatever field is left blank.

so say telephone field before update was 54321
if i update the rest of the fields leaving this one blank (as to say i dont want this changed) it is given a blank value
so when the user presses submit the 54321 is now blank????

do you know of something i could do to stop this from happening?

if not thanks for all your help you have given me its greatly apprecited
snoopgreen

rincewind456

That's what's supposed to happen, if you leave a field blank then SQL won't know if that's what you want or not so it will enter the blank field.

Two ways I can think of to sort it out is, build the sql statement dynamically and only update the rows you want, or ensure that before you enter the data to the db that the empty fields are filled with the data from the select statement.