Officially reached the point of being over my head!

TimUSA · January 5, 2008

ok I have a DB that looks like this the image provided below:

#
# Create Table    : 'series_table'   
# seriesID        :  
# seriesName      :  
#
CREATE TABLE series_table (
    seriesID       INT NOT NULL AUTO_INCREMENT,
    seriesName     VARCHAR(128) NOT NULL,
PRIMARY KEY (seriesID)) TYPE=INNODB;

#
# Create Table    : 'race_table'   
# raceID          :  
# raceName        :  
# raceDate        :  
# hostName        :  
# factor          :  
# boatsInRace     :  
# seriesID        :  (references series_table.seriesID)
#
CREATE TABLE race_table (
    raceID         INT NOT NULL AUTO_INCREMENT,
    raceDate       DATE NOT NULL,
    hostName       VARCHAR(128) NULL,
    factor         DECIMAL NOT NULL,
    boatsInRace    INT NOT NULL,
    seriesID       INT NULL,
PRIMARY KEY (raceID),
    INDEX idx_fk_race_table (seriesID),
CONSTRAINT fk_race_table FOREIGN KEY (seriesID)
    REFERENCES series_table (seriesID)
    ON DELETE SET NULL
    ON UPDATE CASCADE) TYPE=INNODB;

#
# Create Table    : 'pts_table'   
# pts_table_id    :  
# racePoints      :  
# matchPoints     :  
# fleetPoints     :  
# raceID          :  (references race_table.raceID)
# skipperName       :  
#
CREATE TABLE pts_table (
    pts_table_id   INT NOT NULL AUTO_INCREMENT,
    skipperName       VARCHAR(128) NULL,
    position   INT NOT NULL,
    racePoints     INT NOT NULL,
    matchPoints    INT NOT NULL,
    fleetPoints    INT NOT NULL,
    raceID         INT NULL,
    
PRIMARY KEY (pts_table_id),
    INDEX idx_fk_pts_table (raceID),
CONSTRAINT fk_pts_table FOREIGN KEY (raceID)
    REFERENCES race_table (raceID)
    ON DELETE SET NULL
    ON UPDATE CASCADE) TYPE=INNODB;

#
# Create Table    : 'race_screenshots_table'   
# race_screenshots_id :  
# imageURL        :  
# raceID          :  (references race_table.raceID)
#
CREATE TABLE race_screenshots_table (
    race_screenshots_id INT NOT NULL AUTO_INCREMENT,
    imageURL       MEDIUMTEXT NOT NULL,
    raceID         INT NULL,
PRIMARY KEY (race_screenshots_id),
    INDEX idx_fk_race_screenshots_table (raceID),
CONSTRAINT fk_race_screenshots_table FOREIGN KEY (raceID)
    REFERENCES race_table (raceID)
    ON DELETE SET NULL
    ON UPDATE CASCADE) TYPE=INNODB;

now I have created a form with the following code:

//////ENTRY PAGE 3//////

//////CONFIGURATION//////
global $scripturl;


/////ENTRY FORM 2/////

//DISPLAY THE FORM
if (isset($_POST['submitted'])) {
echo'
<form action="'.$scripturl.'?page=18'.$GET['page'].'" method="post">
<INPUT id="submitted" name="submitted" type="hidden" value="TRUE" />';
//HOSTING INFORMATION
echo'
<hr style="width: 100%; height: 2px;" />
<h4>Hosting Information</h4>
        <hr style="width: 100%; height: 2px;" />';
	//Race Information From Page 2
	echo'
	<table>
	<tr>
	<td><p><b>Host :</b></P><input type="text" READONLY name="host" value="' . $_POST['host'] . '" /></td>
	<td><p><b>Date :</b></P><input type="text" READONLY name="date" value="' . $_POST['date'] . '" /></td>
	<td><p><b>Number of Boats :</b></P><input type="text" READONLY name="boats" value="' . $_POST['boats'] . '" /></td>
	<td><p><b>Race Series :</b></P><input type="text" READONLY name="series" value="' . $_POST['series'] . '" /></td>
	<td><p><b>Race Factor :</b></P><input type="text" READONLY name="factor" value="' . $_POST['factor'] . '" /></td>
	</tr>
	</table>';

//DISPLAY SCREENSHOT
echo'
        <hr style="width: 100%; height: 2px;" />	
        <img width="640" height="480" src="' . $_POST['image'] . '" alt="" />
<tr><td>
<p><b>Image Address: </b></P>
<input type="text" READONLY name="image" style="WIDTH: 800px" value="' . $_POST['image'] . '" />
</td></tr>
</table>';

//DISPLAY RESULTS
echo'
<hr style="width: 100%; height: 2px;" />
<h4>CONFIRM RESULTS</h4>
<hr style="width: 100%; height: 2px;" />';


//ladder calculation


	//Setup Loop
	$times = $_POST['boats'];
	$x = 0;

	while ($x < $times) {

	if ($_POST['position'][$x] == "DNF")
	$points = ($_POST['boats'] +1) - $_POST['boats'];

	else if ($_POST['position'][$x] == "DNS")
	$points = ($_POST['boats'] +1) - $_POST['boats'];

	else
	$points = ($_POST['boats'] +2) - $_POST['position'][$x];

	$ladder = $points * $_POST['factor'];

	echo'
	<table>
	<tr>
	<td><p>Skipper: </p><input type="text" READONLY name="name[ ]" value="' . $_POST['name'][$x] . '" /></td>
	<td><p>Finish Position: </p><input type="text" READONLY name="position[ ]" value="' . $_POST['position'][$x] . '" /></td>
	<td><p>Points This Race: </p><input type="text" READONLY name="points[ ]" value="' . $points . '" /></td>
	<td><p>Ladder Points: </p><input type="text" READONLY name="ladder[ ]" value="' . $ladder . '" /></td>
	</tr>
	</table>';
	$x++;
	}

//FINALLY SUBMIT
	echo'
	<hr style="width: 100%; height: 2px;" />
	<h4>Submit Results: </h4>
	<hr style="width: 100%; height: 2px;" />
	<table>
	<tr>
	<td><INPUT type="submit" value="Submit"><INPUT type="reset" value="Reset"></td>
	</tr>
	</table></form>';
}

I need some help getting this form to submit to the DB correctly

so:

to the "race_table"

$_POST['date'] = raceDate
$_POST['host'] = hostName
$_POST['factor'] = factor
$_POST['boats'] = boatsInRace
$_POST['series'] = seriesID ( somehow I need to take seriesName from the post data and make a query that finds the correct ID for that name)

then in the "race_screenshots_table"

$_POST['image'] = imageURL
raceID = the ID that we just created in the "race_table"

last but not in the "points table"

$_POST['name'][$x] = skipperName
$_POST['position'][$x] = position
$points = racePoints
raceID = the ID that we just created in the "race_table"

if $_POST['factor'] = 2 than:

$ladder = matchPoints

if $_POST['factor'] = 1, 3, or 4 than:

$ladder = fleetPoints

I know the basics of inserting things into a DB, but with the FK and stuff here I am a bit lost??? Although I dont expect anyone to write it for me (although would be nice!) I hope that you all can walk me through this. After that I swear I wont bug you for a while!

[attachment deleted by admin]

revraz · January 5, 2008

I didn't look at the code, but first tell us what is it doing or not doing.

I need some help getting this form to submit to the DB correctly

TimUSA · January 5, 2008

well basically the form above is a collection of information that the user inputs from two other forms, now I just need to get this info into the DB as i listed above. sorry if i dont make sense ???

revraz · January 5, 2008

No problem, just wanted to make sure we were talking about the same thing.

Are you familiar with how to do MySQL Queries and how to INSERT new records?

TimUSA · January 5, 2008

yes something like this example:

mysql_query("INSERT INTO series_table (seriesName) VALUES ('{$_POST['series']}')");

revraz · January 5, 2008

Correct, but first you want to validate the data to insure it's what you expect. You don't want someone entering Text where it should be a Number and vice versa. Then you want to use mysql_real_escape_string on it before the actual insert.

TimUSA · January 5, 2008

but first you want to validate the data

all the validation is done on previous pages. actually the only typed input is the No. of boats, and that is validated on the first page. the rest is all done from selection boxes and and an image upload script.

mysql_real_escape_string

??? Reading the manual

revraz · January 5, 2008

Armed with that info, add a connection string to the server and db, then do your insert query statement. Verify the result and echo out "Record Added" or something similiar.

deadimp · January 5, 2008

all the validation is done on previous pages. actually the only typed input is the No. of boats, and that is validated on the first page. the rest is all done from selection boxes and and an image upload script.

As revraz said, you might still need to do some solid sanitization of your input at the point of processing. Though you may be validating your input via combo boxes or javascript, there are still ways people can submit information past those security measures.

For instance, someone can send a request to that page and hand-type the POST data, or create an external form directly to that last page, and if you don't sanitize the raw input from POST there's a chance that the information could be dirty.

As for the interfacing with MySQL from PHP, you're on the right track. Just look into different libraries and see which flavors you like. You can use the mysql library which has the common 'mysql_query()' functions, mysqli which is a more object-oriented interface, etc.

TimUSA · January 5, 2008

so my example would look like this:

mysql_query("INSERT INTO series_table (seriesName) VALUES ('{$_POST['series']}')",

mysql_real_escape_string($_POST['series']));

add a connection string to the server and db

not needed already connected within SMF

then do your insert query statement

does PHP read line by line? meaning:

first post to race_table

then get the race ID

then post to race_screenshots_table

then post to pts_tabe???

revraz · January 5, 2008

you want $series=mysql_real_escape_string($_POST['series'])); to come before

mysql_query("INSERT INTO series_table (seriesName) VALUES ('$series')",

PHP executes in the order you tell it

deadimp · January 5, 2008

You're example ought to look like this, if you're trying to sanitize it before you insert it:

$temp=mysql_real_escape_string($_POST['series']); //Just store it on a temp - You can change this around
$q="insert into `series_table` (`seriesName`) values ('$temp')";
mysql_query($q);

That ought to work. The extra variables are just for a little organization. If you want to not use temporaries, you can do something like this:

mysql_query("insert into `series_table` (`seriesName`) values ('".mysql_real_escape_string($_POST['series'])."'");

EDIT: Eh, revraz beat me to it.

does PHP read line by line?

If you're asking whether the mysql library (the one you're using now) can execute multiple statements (usually on different lines), then no - though mysqli can. You'll need to have a special function, or split your statements across multiple queries.

TimUSA · January 5, 2008

thanks all....I am gonna hit the drawing board here.

TimUSA · January 6, 2008

Im lost on a couple of things here.

1. Am not sure how to mysql_real_escape_string this with the proper syntax

2. When the form is submitted, it is not returning a value???

if (isset($_POST['submitted'])) {
$seriesresult = mysql_query("
SELECT seriesID
FROM series_table
WHERE seriesName = '{$_POST['series']}'
");
while ($row = mysql_fetch_row($seriesresult)){
$seriesID = $row['seriesID'];
echo $seriesID;
}

revraz · January 6, 2008

mysql_fetch_row returns a numerical array, and you are trying to echo an associate array.

$seriesID = $row[0];

TimUSA · January 6, 2008

thanks think i got this right!

if (isset($_POST['submitted'])) {
mysql_real_escape_string($_POST['series']);
$seriesresult = mysql_query("
SELECT seriesID
FROM series_table
WHERE seriesName = '{$_POST['series']}'
");
while ($row = mysql_fetch_row($seriesresult)){
$seriesID = $row[0];
}
echo $seriesID;
}

Ken2k7 · January 6, 2008

I need some help getting this form to submit to the DB correctly

so:

to the "race_table"

$_POST['date'] = raceDate

$_POST['host'] = hostName

$_POST['factor'] = factor

$_POST['boats'] = boatsInRace

$_POST['series'] = seriesID ( somehow I need to take seriesName from the post data and make a query that finds the correct ID for that name)

then in the "race_screenshots_table"

$_POST['image'] = imageURL

raceID = the ID that we just created in the "race_table"

last but not in the "points table"

$_POST['name'][$x] = skipperName

$_POST['position'][$x] = position

$points = racePoints

raceID = the ID that we just created in the "race_table"

if $_POST['factor'] = 2 than:

$ladder = matchPoints

if $_POST['factor'] = 1, 3, or 4 than:

$ladder = fleetPoints

I know the basics of inserting things into a DB, but with the FK and stuff here I am a bit lost??? Although I dont expect anyone to write it for me (although would be nice!) I hope that you all can walk me through this. After that I swear I wont bug you for a while!

Check this out.

To insert something to multiple tables, you just change your query. "INSERT INTO table_name (columns) VALUES ('values')"

So if you want to insert multiple things, just do that. And you can always check the $_POST['factor'] before calling the query.

So you can do these in parts. No one said you have to insert them all in one query. Not sure if you can. I'm not an expert in PHP and I don't claim to be.

to the "race_table"

$_POST['date'] = raceDate

$_POST['host'] = hostName

$_POST['factor'] = factor

$_POST['boats'] = boatsInRace

$_POST['series'] = seriesID ( somehow I need to take seriesName from the post data and make a query that finds the correct ID for that name)

You can check via sql queries.

<?php
//  EDIT CONNECTION AND DATABASE SELECTION //
$db_connect = mysql_connect("server", "username", "password") or die(mysql_error());
mysql_select_db("database_name") or die(mysql_error());

mysql_query("INSERT INTO race_table (raceDate, hostName, factor, boatsInRace, seriesID) VALUES ('{$_POST['date']}','{$_POST['host']}','{$_POST['factor']}','{$_POST['boats']}','{$_POST['series']}') WHERE seriesName='{$_POST['series']}'",$db_connect) or die(mysql_error());
?>

then in the "race_screenshots_table"

$_POST['image'] = imageURL

raceID = the ID that we just created in the "race_table"

<?php
// ASSUMING YOU PUT THIS BELOW THE CODE ABOVE //
mysql_query("INSERT INTO race_screenshots_table (imageURL,raceID) VALUES ('{$_POST['image']}','{$_POST['series']}')",$db_connect) or die(mysql_error());
?>

last but not in the "points table"

$_POST['name'][$x] = skipperName

$_POST['position'][$x] = position

$points = racePoints

raceID = the ID that we just created in the "race_table"

if $_POST['factor'] = 2 than:

$ladder = matchPoints

if $_POST['factor'] = 1, 3, or 4 than:

$ladder = fleetPoints

Query is a string. You can concatenate or alter them however you want.

<?php
// ASSUMING YOU PUT THIS BELOW THE CODE ABOVE //
$query = "INSERT INTO points_table (skipperName, position, racePoints, raceID, _%rep_) VALUES('{$_POST['name'][$x]}','{$_POST['position'][$x]}','$points','{$_POST['series']}','$ladder')";

if ($_POST['factor'] == 2) str_replace("_%rep_", "matchPoints", $query);
else str_replace("_%rep_", "fleetPoints", $query);
mysql_query($query,$db_connect) or die(mysql_error());
?>

So does that help at all?

Sign In

Officially reached the point of being over my head!

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information