saving values to MySQL

[wkilc]

Hello all,

 

I'm pretty good with HTML, but am still learning PHP... so I may sound kinda ignorant here.  Apologies in advance.

 

I'm creating a relatively simple PHP page that displays data from a MySQL database (this I've done before)... the data will be presented in a "sort-able" table, with each cell filled with info from the database.  One column in this table will have no info, just empty text fields... folks will use their web browser to fill in values into this empty column and then click a button (below the table) to re-save the new values to the MySQL database.

 

Here's the page:

http://menc-eastern.org/test/

 

Here's the code from the PHP include that is supposed to update the database:

 

<?php

$rank = $_POST['rank'];

mysql_connect("localhost:/tmp/mysql5.sock", 'username', '**********'); mysql_select_db("database_name");

$query="INSERT INTO database_table (rank) VALUES ('".$rank."')";

mysql_query($query) or die ('Error updating database');

echo "Database updated";

?>

 

I "pieced" together that code from some tutorials, as I didn't know what exactly what to do.  If you click the "Update!" button below the table, it appears like it works, but no values are stored.

 

The other thing that is a problem... is that if you enter a value into one of the "Rank" text fields on the form, and then re-sort the table, the values are lost, they all become blank again.

 

Thanks for reading.

 

~Wayne

[craygo]

looking at your source code you did not give any of your input fields a name. so no data is being passed to the query. You will need to store the data into an array.

 

<input name="rank[]" type="text" size="6">

 

then once you click the submit you can format the array for insert. But you need to loop through the values to get them to insert. Also if user will be ranking them you will need more than just the rankings. you will have to pass along a user id or something.

 

foreach($_POST['rank'] as $rank){
$query="INSERT INTO database_table (rank) VALUES ('$rank')";
$res = @mysql_query($query);
if(!$res){
echo "Could not insert Rank $rank<br>SQL Error: ".mysql_error()."\n";
}
}

 

Ray

 

[wkilc]

Thank you! 

 

I am embarrassed that I missed the form name... that I should have known.

 

I think I understand what you mean about a user ID, so it knows which student the rank applies to... if it were a "static" HTML form, I guess I called them "student1", "rank1", "student2", "rank2", etc...

 

But because the table is generated by the number of students in the MySQL table, I'm not sure (okay, I have no idea) how to do that.

 

This is what the include "form" script looks like now:

 

<?php

$rank = $_POST['rank'];

mysql_connect("localhost:/tmp/mysql5.sock", 'username, '********'); mysql_select_db("db_name");

foreach($_POST['rank'] as $rank){
$query="INSERT INTO db_table (rank) VALUES ('$rank')";
$res = @mysql_query($query);
if(!$res){
echo "Could not insert Rank $rank<br>SQL Error: ".mysql_error()."\n";
}
}

mysql_query($query) or die ('Error updating database');

echo "Database updated";

?>

 

I hate to post this much code... but just in case the complete code on the form page if it is needed:

 

<strong>President's Stuff</strong>
<br/>

<?
  function pparams($sort) {
    global $state_aff, $sponsor, $school, $student, $instrument, $rank, $comments;

    echo $HTTP_SERVER_VARS['PHP_SELF'];
    echo "?state_aff=".htmlentities(urlencode($state_aff));
    echo "&sponsor=".htmlentities(urlencode($sponsor));
    echo "&school=".htmlentities(urlencode($school));
    echo "&student=".htmlentities(urlencode($student));
    echo "&instrument=".htmlentities(urlencode($instrument));
    echo "&rank=".htmlentities(urlencode($rank));
    echo "&comments=".htmlentities(urlencode($comments));
    echo "&sort=".htmlentities(urlencode($sort));
  }

  if (!$sort) $sort="sponsor";
?>

<form method="post" action="../php_includes/update_db.php">

<br>
<font size="1">Click on any category below to re-sort the list:
<table border="1" cellspacing="0" cellpadding="2" width="710">
<tr>
<td><b><a href="<? pparams("state_aff"); ?>">State MEA</a></b></td>
<td><b><a href="<? pparams("sponsor"); ?>">Sponsor</a></b></td>
<td><b><a href="<? pparams("school"); ?>">School</a></b></td>
<td><b><a href="<? pparams("student"); ?>">Student</a></b></td>
<td><b><a href="<? pparams("instrument"); ?>">Instrument</a></b></td>
<td><b><a href="<? pparams("rank"); ?>">Rank</a></b></td>
<td><b><a href="<? pparams("comments"); ?>">Comments</a></b></td></tr>

<? include("../includes/connect_db_name.inc.php");   ?> 

<? $query =
        "SELECT db_table.state_aff,db_table.sponsor,db_table.school,db_table.student,db_table.instrument,db_table.rank,db_table.comments
        FROM db_table
        WHERE ((db_table.state_aff LIKE '$state_aff%')
      and (db_table.sponsor LIKE '$sponsor%')
              and (db_table.school LIKE '$school%')
              and (db_table.student LIKE '$student%')
   and (db_table.instrument LIKE '$instrument%')
   and (db_table.instrument LIKE '$instrument%')
   and (db_table.rank LIKE '$rank%')
   and (db_table.comments LIKE '$comments%'))
        ORDER BY db_table.$sort";
$resultt = mysql_query($query);

// Determine the number of students
$number = mysql_num_rows($resultt);

if ($number == 0) {
   print "<strong>Sorry, there were no records matching those criteria</strong>;
} else {
while ($row = mysql_fetch_object($resultt)) {
    $state_aff=$row->state_aff;
    $sponsor=$row->sponsor;
    $school=$row->school;
    $student=$row->student;
    $instrument=$row->instrument;
    $rank=$row->rank;
    $comments=$row->comments;


print "<tr><td>$state_aff</td>
<td>$sponsor</td>
<td>$school</td>
<td>$student</td>
<td>$instrument</td>
<td><input type='text' name='rank[]' size='6' value='$rank'></td>
<td>$comments</td></tr>";
   }
}

// Close the databaas connection
mysql_close();

?>

</table>

<br />
<input type="submit" value="Update!" />
</form>

 

Thanks again.

 

~Wayne