PHP Form -> Database Query

I need confirmation if this will work!

[Maelgwyn]

Here's the code I've come up with that works:

<form action="<?php echo($php_self);?>"
method=post>
<p>Enter the CD details:<br />
CD Title:&nbsp;&nbsp; <textarea name="cdtitle" rows="1"></textarea><br />
CD Artist:&nbsp;&nbsp;&nbsp; <select name="cdartist">
<?php
$sql = "SELECT * FROM tblArtist";
$result = mysql_query($sql);
WHILE ($artist = mysql_fetch_row($result))
{
echo("<option value='$artist[0]'>$artist</option>");
}
?>
</select>
<br />
CD Record Company: <select name="cdrecordco">
<?php
$sql = "SELECT * FROM tblRecordCo";
$result = mysql_query($sql);
WHILE ($record = mysql_fetch_row($result))
{
echo("<option value='$record[0]'>$record[1]</option>");
}
?>
</select>
<br />
CD Genre:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<select name="cdgenre">
<?php
$sql = "SELECT * FROM tblGenre";
$result = mysql_query($sql);
WHILE ($genre = mysql_fetch_row($result))
{
echo("<option value='$genre[0]'>$genre[1]</option>");
}
?>
</select>
<br />
CD Setting:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<select name="cdsetting">
<?php
$sql = "SELECT * FROM tblSetting";
$result = mysql_query($sql);
WHILE ($setting = mysql_fetch_row($result))
{
echo("<option value='$setting[0]'>$setting[1]</option>");
}
?>
</select>
<br />
CD has multimedia? <input type="radio" name="multimedia" value="yes">Yes &nbsp;<input type="radio" name="multimedia" value="no">No<br />
<input type="submit" value="submit">
<input type="reset" value="reset">
</form>

Now, I've written this out, and I want to know if anybody thinks this will work, and if not, what I need to do so it does work!

if ("SUBMIT" == $submitform) {
$sql = "INSERT INTO tblArtist, tblCD, tblRecordCo, tblGenre, tblSetting SET " .
"CD_Title" = '$cdtitle', " .
"Artist_Name" = '$artist', " .
"RecordCo" = '$record', " .
"Genre" = '$genre', " .
"Multimedia" = '$mvalue', " .;

if (mysql_query($sql)) {
echo("<p> Thank you</p>");
}
else {
echo("<p>There was an error!</p>");
}
}

I've got a few queries here -
1) how do I set the value of the radio button so I can enter that into the database?
2) I'm pretty certain the code I've got is really incorrect... Please help!

Basically what's happening is that I've got a DB for cd's, tblArtist is obviously the Artist table, tblRecordCo is the Recording Company, tblGenre is the Genre, tblSetting is the Setting (Live/Studio Album/Unplugged/Single etc), tblCD incorporates those tables into it, and there's also another table for Tracklistings, but I've got no idea where to start for that one!

Oh yeah, and I know the &nbsp;'s are really ugly!

Thanks!

Nik

[1][edited by: coopster at 12:03 pm (utc) on July 27, 2005]
[edit reason] no urls, no signatures please [/edit]

[ergophobe]

Welcome Nik.

The easy way to know whether something works is to try it. Have you tried it?

[Maelgwyn]

Yeah I have... I get:

Parse error: parse error, unexpected '='

Of course, I'm doing this from within Typo3, so I can't see what the line numbers are, or even if I count the lines of php code, if it'll be the correct line! :/

[ergophobe]

That parse error could come from her

"Artist_Name" = '$artist', " .
"RecordCo" = '$record', " .
"Genre" = '$genre', " .
"Multimedia" = '$mvalue', " .;

You don't want to close your quotes before the equals. Effectively, what you have is

$sql = "literalt string" = 'literal string';

and that won't work. You don't actually need to close the quotes just because you're at the end of the line.

$sql = "INSERT INTO tblArtist, tblCD, tblRecordCo, tblGenre, tblSetting SET
CD_Title='$cdtitle',
etc

will work just fine as far as parsing by the PHP parser goes.

Unfortunately, it's going to blow up when it gets passed off to the MySQL server. You can't insert into several tables with one query. You need to INSERT, then figure out what the ID is, then use that as the key for your subsequent inserts. PLus the syntax you have is more for an UPDATE than an INSERT. INSERTs look like this

INSERT INTO my_table_name (column1, column2, column3) VALUES ('value1', 'value2', 'value3');

Then you can select for LAST_INSERT_ID using

$sql = "SELECT LAST_INSERT_ID()";

You can also get the id using mysql_insert_id() provided that you do NOT have a column type of BIGINT in your auto-increment column. See the manual [php.net] for more info on that.

Once you have your insert id, rinse, lather and repeat.

Check the MySQL manual and read up on INSERT and UPDATE. I think that will help.