Posting an array to a database

[BelowZero]

I'm trying to figure out how to post an array into my database. I am able to post single entries without a problem, but trying to post several entries at once is causing me some headaches. Can anyone check this code and hopefully give me some insight as to where I'm going wrong?

 

Here's the form:

<?php include("opendatabase.php"); ?>

<FORM Method = "POST" action ="insert_spreads.php">

<?php
$weekID = $_POST[Week];
echo "<h2>Enter Spreads for Week $weekID</h2>";
print ("<br /><br />");

$result = mysql_query("
SELECT S.game_id, TH.team_name AS HomeTeam, TA.team_name AS AwayTeam
FROM schedule AS S 
JOIN teams AS TH ON S.H_team = TH.team_id
JOIN teams AS TA ON S.A_team = TA.team_id
WHERE S.week_id = '$weekID'
ORDER BY S.game_id;");

while ($row = mysql_fetch_array($result)) {
printf('<input type="text" size="4" name="w%dg%dAspread">', $weekID, $row['game_id']);
printf(" %s vs. %s  ", $row['AwayTeam'], $row['HomeTeam']);
printf('<input type="text" size="4" name="w%dg%dHspread">', $weekID, $row['game_id']);
print("<br /><br />");
} 
mysql_close($con);
?>
<br /><br /> 

<input type="Submit" value="Submit Spreads">
</FORM>	

 

And here's the "insert_spreads.php

<?php	

header("Location: admin_main_entry.php");

include("opendatabase.php");

$aspread=$_POST['w%dg%dAspread'];
$hspread=$_POST['w%dg%dHspread'];
$row=$_POST[$row['game_id'];

$sql="
UPDATE schedule 
SET A_pt_spread= '$aspread',H_pt_spread= '$hspread'
WHERE week_id = '$weekID'
AND game_id = '$row'";

$result = mysql_query($sql);

mysql_close($con)

?>

 

Thanks!

[Muddy_Funster]

not really sure what's going on here, or what your getting at about posting multiple entries, your code is only set to post a sigle update and there is nothing about an array that I can identify.  What exactly are you looking to achieve? and what EXACTLY is the problem that you are having (e.g error code, blank page, runs through but result differs from that which is desired by....)

[BelowZero]

I'm trying to gather several numbers from 1 page and post them to the database all at once. This is what the page looks like:

 

www.beat-the-spread.net/admin_pre_spreads.php

 

Choose Week 1 and it brings up all the games for that week with input areas to place the point spreads.

Once I enter the spreads, I'm not sure how to get them posted to  the database. They should be posted to fields "A_pt_spread" and "H_pt_spread" in the table "schedule". I also have fields "week_id" and "game_id" to point to the correct row.

Right now I'm not getting any errors but nothing is getting posted.

 

Thanks

[tomtimms]

You would need to put each input element into an array.  So ..

 

<?php include("opendatabase.php"); ?>

<FORM Method = "POST" action ="insert_spreads.php">

<?php
$weekID = $_POST[Week];
echo "<h2>Enter Spreads for Week $weekID</h2>";
print ("<br /><br />");

$result = mysql_query("
SELECT S.game_id, TH.team_name AS HomeTeam, TA.team_name AS AwayTeam
FROM schedule AS S 
JOIN teams AS TH ON S.H_team = TH.team_id
JOIN teams AS TA ON S.A_team = TA.team_id
WHERE S.week_id = '$weekID'
ORDER BY S.game_id;");


$i = 0; //Counter

while ($row = mysql_fetch_array($result)) {
printf('<input type="text" size="4" name="w%dg%dAspread[' . $i . ']">', $weekID, $row['game_id']);
printf(" %s vs. %s  ", $row['AwayTeam'], $row['HomeTeam']);
printf('<input type="text" size="4" name="w%dg%dHspread[' . $i . ']">', $weekID, $row['game_id']);
print("<br /><br />");

$i++;
} 
mysql_close($con);
?>
<br /><br /> 

<input type="Submit" value="Submit Spreads">
</FORM>	

 

Then you would need to do a foreach in your form processing file.

[sdowney1]

you could insert the values of an array as strings separated by a chr(29)

then when getting them back split them back out as an array using explode around the chr(29)

 

[Muddy_Funster]

you can't post anything that's not in the form - even a datavase result value :

$row=$_POST[$row['game_id'];

won't work.

you will need to make a hidden field on your form and assign it this value to take it through with the post command.  alternativly parse it in the URL header and use a $_GET[] to retieve it.  I assume if you use a static value for game_id then it works ok?

[DavidAM]

The problem we have here, is that your input fields are not an array. (Well, they are all in the $_POST array, but they are individual entries there), so you have to process each one. To make it even more difficult, when the form is POSTed, the only way you know the week and game IDs is by picking apart the field names; this is going to be a pain.  Let's see if we can offer a more flexible solution.

 

First, let's look at what we currently have. The fields where you enter the spreads are posted (in the $_POST array) as -- assuming week 2 of the season -- w2g1Aspread, w2g1Hspread, w2g2Aspread, w2g2Hspread, w2g3Aspread, w2g3Hspread, ... and so on. Let's instead post the whole thing as an array of spreads. Hold on to your hats, this is advanced stuff ...

 

When we generate the fields let's use this code to process the database resultset:

while ($row = mysql_fetch_array($result)) {
printf('<input type="text" size="4" name="spread[%d][%d][A]">', $weekID, $row['game_id']);
printf(" %s vs. %s  ", $row['AwayTeam'], $row['HomeTeam']);
printf('<input type="text" size="4" name="spread[%d][%d][H]">', $weekID, $row['game_id']);
print("<br /><br />");
} 

Notice the way we build up the name for the INPUT tag. Now, the %-symbols will NOT be in the name sent to the browser. The browser should actually see something like this (again, week 2):

<input type="text" size="4" name="spread[2][1][A]"> Team-1 vs. Team-2 <input type="text" size="4" name="spread[2][1][H]"><br /><br />
<input type="text" size="4" name="spread[2][2][A]"> Team-3 vs. Team-6 <input type="text" size="4" name="spread[2][2][H]"><br /><br />
<input type="text" size="4" name="spread[2][3][A]"> Team-5 vs. Team-4 <input type="text" size="4" name="spread[2][3][H]"><br /><br />

Normally, when creating an array with fields, we just put the array brackets "[]" on the end of the name, and the array is sequentially numbered. However, it is permitted to put real values in the array brackets and get those values back. So, when we post this form, we get something like this for $_POST['spread']:

Array (
    [2] => Array (
            [1] => Array (
                    [A] => 1
                    [H] => 2
                )
            [2] => Array (
                    [A] => 3
                    [H] => 4
                )
            [3] => Array (
                    [A] => 5
                    [H] => 6
                )
        )
)

Stick with me, we're almost there ... Notice that the first element of the array is "2", which is our week number. There are several elements of that array, keyed by our game number, and each of those has two elements for Away and Home. So, to stick this in the database, we can do something like this:

 

foreach ($_POST['spread'] as $weekID => $games) {
  foreach ($games as $gameID => $values) {
    $sql = 'INSERT A_pt_spread = ' . $values['A'] . ', H_pt_spread = ' . $values['H'] .
        ' WHERE week_id = ' . $weekID . ' and game_id = ' . $gameID;
    mysql_query($sql);

 

Hope this helps.

[BelowZero]

Thank you, gentlemen. Your help is appreciated.

 

David, I've been studying your code and I understand the logic. I'm just not familiar enough with all the syntax and expressions to do this on my own. Thanks for spelling it out in such detail.

 

However, I'm still not getting anything posted to the database.

 

I noticed in this code that there are 2 open brackets that never get closed. I'm assuming that they need to close. Wondering if that could be the problem.

foreach ($_POST['spread'] as $weekID => $games) {  foreach ($games as $gameID => $values) {    $sql = 'INSERT A_pt_spread = ' . $values['A'] . ', H_pt_spread = ' . $values['H'] .        ' WHERE week_id = ' . $weekID . ' and game_id = ' . $gameID;    mysql_query($sql);

 

Thanks.

[DavidAM]

Oops! Yeah they should get closed right after the call to mysql_query().

foreach ($_POST['spread'] as $weekID => $games) {
  foreach ($games as $gameID => $values) {
    $sql = 'INSERT A_pt_spread = ' . $values['A'] . ', H_pt_spread = ' . $values['H'] .
        ' WHERE week_id = ' . $weekID . ' and game_id = ' . $gameID;
    mysql_query($sql);
  }
}

Unless, of course, you need to do more work in there.

[BelowZero]

ugh...

 

This still won't write to the database.

I have echoed all the values and they are all just the way I entered them, so I know it's passing the values, but nothing happens to the database. Can someone look at this code for any problems? Thanks.

 

<?php	

header("Location: admin_main_entry.php");

include("opendatabase.php");

foreach ($_POST['spread'] as $weekID => $games) 
{
foreach ($games as $gameID => $values) 
{
	$sql = 'INSERT 
	A_pt_spread = ' . $values['A'] .',
	H_pt_spread = ' . $values['H'] .' 
	WHERE week_id = ' . $weekID .  ' 
	AND game_id = ' . $gameID ;

	mysql_query($sql);
}
}

mysql_close($con)

?>

[Pikachu2000]

Are you trying to INSERT a new record, or UPDATE an existing record?

[BelowZero]

Ah yes, good point. I'm updating existing records.

[MikeDean89]

Your latest code is missing a few things from your query.

 

- You have no table selected, e.g. an insert query should start like - INSERT INTO table_name SET value_1=.. (there is a different way but this is how I do it ).

- As you've got a WHERE clause, it seems that you really want to update. So you should change your query to - UPDATE table_name SET etc.

- If you do really want to do an INSERT, remove the WHERE and add a comma before week_id and change AND to a comma. It should end up something like this:

 

<?php	

header("Location: admin_main_entry.php");

include("opendatabase.php");

foreach ($_POST['spread'] as $weekID => $games) 
{
foreach ($games as $gameID => $values) 
{
	$sql = 'INSERT INTO change_to_table_name SET 
	A_pt_spread = ' . $values['A'] .',
	H_pt_spread = ' . $values['H'] .', 
	week_id = ' . $weekID .  ', 
	game_id = ' . $gameID ;

	mysql_query($sql);
}
}

mysql_close($con)

?>

 

Hope this helps.

 

Mike.

 

Edit: Just seen that you want to update.

 

Start your query off with UPDATE change_to_table_name SET and it should work fine.

 

Mike.

[Pikachu2000]

You will also need a WHERE with an UPDATE query, or it will update every record in the table . . .

[Psycho]

You should NEVER run queries in a loop. You should use a loop to create the code for one single INSERT query to insert all the queries at once. Here is an example:

//Create array to store all insert records
$insertRecords = array();
foreach ($_POST['spread'] as $weekID => $games) 
{
    foreach ($games as $gameID => $values) 
    {
        $insertRecords[] = "('{$weekID}', '{$gameID}', '{$values['A']}', '{$values['H']}')\n";
    }
}

//Create and run single query to insert all the records
$sql = "INSERT INTO change_to_table_name (week_id, game_id, A_pt_spread, H_pt_spread)
        VALUES " . implode(", ", $insertRecords);
mysql_query($sql);

[BelowZero]

Okay mjdamato, this inserted my data into the database just fine.

 

But I actually need to UPDATE just the "A_pt_spread" and "H_pt_spread" WHERE the "game_id" and "week_id" match my entries. I tried changing your code but it didn't work...

 

<?php	

header("Location: admin_main_entry.php");

include("opendatabase.php");

//Create array to store all insert records
$insertRecords = array();
foreach ($_POST['spread'] as $weekID => $games) 
{
foreach ($games as $gameID => $values) 
{
$insertRecords[] = "('{$values['A']}', '{$values['H']}')\n";
}
}

//Create and run single query to insert all the records
$sql = "UPDATE schedule 
SET " . implode(", ", $insertRecords);
WHERE week_id = ' . $weekID .  ' AND game_id = ' . $gameID ; 
mysql_query($sql);

mysql_close($con)

?>

 

How can I change this code to update specific rows?

Thanks.

[DavidAM]

BelowZero:  I'm sorry, I don't know why I put an INSERT statement in the code I posted, it was clear you were doing an UPDATE. I don't know how I mixed that up. I even remember thinking about mjdamato's point about running queries in a loop, but I could not see a straight-forward way to do UPDATE in a batch similar to his INSERT suggestion. The code I suggested should have been:

foreach ($_POST['spread'] as $weekID => $games) {
  foreach ($games as $gameID => $values) {
    $sql = 'UPDATE schedule SET A_pt_spread = ' . $values['A'] . ', H_pt_spread = ' . $values['H'] .
        ' WHERE week_id = ' . $weekID . ' and game_id = ' . $gameID;
    mysql_query($sql);
  }
}

I apologize for sending you down that rabbit trail.

 

As to the "never run queries in a loop" statement; you could build a single UPDATE statement from the posted values and run it after the loop. IMO that is an advanced solution and I leave it as an exercise for the user. I will say that the final query would (possibly) look something like this (for 3 games);

UPDATE schedule 
SET A_pt_spread = CASE(game_id WHEN 1 THEN 5 WHEN 2 THEN 6 WHEN 3 THEN 7 END), 
H_pt_spread = CASE(game_id WHEN 1 THEN 3 WHEN 2 THEN 4 WHEN 3 THEN 5 END) 
WHERE week_id = 1 
and game_id IN  (1, 2, 3)

[BelowZero]

David, I'm not worried about "rabbit trails". For me, it is through the mistakes that I learn. And I'm learning a lot about coding and different ways to approach problems. I do appreciate your help and advice.

 

I updated my "insert_spreads" file with this code:

 

<?php	

header("Location: admin_main_entry.php");

include("opendatabase.php");

foreach ($_POST['spread'] as $weekID => $games)
{
foreach ($games as $gameID => $values) 
{
$sql = 'UPDATE schedule SET A_pt_spread = ' . $values['A'] . ', H_pt_spread = ' . $values['H'] .' 
WHERE week_id = ' . $weekID . ' and game_id = ' . 	$gameID;
mysql_query($sql) or die("Problem in this line");
}
}

mysql_close($con)

?>

 

Unfortunately, it does die! Since I've already echoed all the variables successfully, could there be a problem in the syntax? Thanks.

[mattal999]

mysql_query($sql) or die("Could not update records because: ".mysql_error());

Oh how we all love this line. Post back the result.

[BelowZero]

Here's the error message:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' H_pt_spread = 5 WHERE week_id = 1 and game_id = 1' at line 1

 

It looks correct to me...Do I need some quotes in there or something??

[mattal999]

Does the word "and" need to be in capitals?

[Pikachu2000]

No, upper case isn't the issue. Change the error report to include the whole query string and post the result.

mysql_query($sql) or die( "<br>Query: $sql<br>Failed with error: " . mysql_error() );

[BelowZero]

I figured out that I'm getting the error because I wasn't filling in ALL the input boxes. I thought I had it set up so that I only had to enter 1 spread per game. If I enter a number for the favorite and a "0" for the underdog it works fine.

 

Of course that begs the question, How do I make all the unentered boxes = 0?

 

[mattal999]

I'm going to post this although I already know that someone else will post a more compact and efficient solution...

 

foreach ($_POST['spread'] as $weekID => $games)
{
foreach ($games as $gameID => $values) 
{
if(empty($values['A'])) $values['A'] = 0;
if(empty($values['B'])) $values['B'] = 0;
$sql = 'UPDATE schedule SET A_pt_spread = ' . $values['A'] . ', H_pt_spread = ' . $values['H'] .' 
WHERE week_id = ' . $weekID . ' and game_id = ' . 	$gameID;
mysql_query($sql) or die("Problem in this line");
}
}

[Pikachu2000]

You should be checking to see if the value is present in the $_POST array before attempting to assign it, and if it is not, you can then assign it as zero.