Need help updating database with variables

[pickett65]

I have a script that reads all rows from a database, and prints it out to an html table. The html table has an extra column for "status".In this column there is a form that is supposed to submit the dropdown field to the mysql database. I've got the set up so it updates the database IF I give the processing script a static id number.

My question is...

How do I make the update_db.php script work If i need a variable for row number?

<?php

$hostname="hostname";
$username="username";
$password="password";
$db="database";
$entry= $_POST['status'];



// Connect to Mysql
$global_dbh = mysql_connect($hostname, $username, $password)
or die("Could not connect to database");
//Select mysql database
mysql_select_db($db, $global_dbh)
or die("Could not select database");


mysql_query("UPDATE cold_call_data SET status = $entry
WHERE id = $id ")
or die("Could not update database!");

echo "\ndatabase updated successfully!";

?>

[ddymacek]

since your script is reading in the id of the records when you are creating your form / table, why not pass in the id in either a hidden field, or attach it in your drop down list. you can make the 'value' of your select list be $id-'status' while only displaying the shortened 'status' to the end user. so
<select name='status'>
<option value='$recordid-No Answer'>No Answer</option>
<option value='$recordid-Connected'>Connected</option>
</select>
and in you php
$work= $_POST;
$pieces = explode("-",$work);
$id = $pieces[0];
$entry = $pieces[1];
// if you are using - in your status' then a : colon could separate your id:status
// you would then explode on : instead of -

[pickett65]

I tried your idea. But upon submitting i still got could not update database.
Ill post the table/form script. Maybe I have that set up wrong...

$hostname="";
$username="";
$password="";
$db="";
$global_dbh = mysql_connect($hostname, $username, $password)
or die("Could not connect to database");
mysql_select_db($db, $global_dbh)
or die("Could not select database");

function display_db_query($query_string, $connection,
$header_bool, $table_params)
{
// perform the database query
$result_id = mysql_query($query_string, $connection)
or die("display_db_query:" . mysql_error());
// find out the number of columns and rows in result
$column_count = mysql_num_fields($result_id)

or die("display_db_query:" . mysql_error());
$row_count= mysql_num_rows($result_id);
// Here the table attributes from the $table_params variable are added
print("<TABLE $table_params >\n");
print("<th>State</th>
<th>Company Name</th>
<th>Phone</th>
<th>contact first</th>
<th>contact last</th>
<th>title</th>
<th>status</th>");
// optionally print a bold header at top of table
if ($header_bool)
{
print("<TR>");

for ($column_num = 0;
$column_num < $column_count;
$column_num++)
{
$field_name =
mysql_field_name($result_id, $column_num);


}
print("</TR>\n");
}
// print the body of the table
while ($row = mysql_fetch_row($result_id))
{
print("<TR ALIGN=LEFT VALIGN=TOP>");
for ($column_num = 0;
$column_num < $column_count;
$column_num++)
{
print("<TD>$row[$column_num]</TD>\n");
}
print("<td>");

print("<form name=\"status\" action=\"updateDB.php\" method=\"post\"><select>
<option value=Call Me selected>Call Me!</option>
<option value=Called-No Message>Called-No Message</option>
<option value=Called-Left Message>Called-Left Message</option>
<option value=Called-No Interest>Called-No Interest</option>
<option value=Prospect Call Back>Prospect Call Back</option>
<option value=Prospect Analysis>Prospect Analysis</option>
<option value=Prospect Quote>Prospect Quote</option>
<option value=Do Not Call>Do Not Call</option>

</select><input type=\"submit\" value=\"Submit status\" /></form><br/><br/>row:\n"); 

echo "</select>";// Closing of list box 
print("</td>");

print("</TR>\n");
}

print("</TABLE>\n"); }



function display_db_table($tablename, $connection,
$header_bool, $table_params)
{

//check if the starting row variable was passed in the URL or not
if (!isset($_GET['pn']) or !is_numeric($_GET['pn'])) {
  //we give the value of the starting row to 0 because nothing was found in URL
  $startrow = 0;
//otherwise we take the value from the URL
} else {
  $startrow = (int)$_GET['pn'];
}

$query_string = "SELECT id, state, company_name, phone, contact_first, contact_last, title FROM $tablename ORDER BY state ASC LIMIT $startrow, 20";
display_db_query($query_string, $connection,
$header_bool, $table_params);

}
?>

<TABLE width="50" cellpadding="12" cellspacing="12">


<TR><TD>
<?php
//In this example the table name to be displayed is  static, but it could be taken from a form
$table = "cold_call_data";

display_db_table($table, $global_dbh,
TRUE, "border='2'");
?>
</TD>

</TR></TABLE>



<?php echo $paginationDisplay; ?>
</body>
</html>

[pickett65]

so after pondering what ddymacek had said, I rebuilt the table display form script. The new one is much simpler and is passing the id variable through the query to the database. I am inserting the id variable in a hidden field on the form. It appears to be working but i am still getting an "unable to update database!" error before the updateDB.php script finishes running. Anyway, Here it is:

display_table.php

<?php
$hostname="";
$username="";
$password="";
$db="";
$global_dbh = mysql_connect($hostname, $username, $password)
or die("Could not connect to database");
mysql_select_db($db, $global_dbh)
or die("Could not select database");



$result = mysql_query("SELECT id, state, company_name, phone, contact_first, contact_last, status, title FROM cold_call_data ORDER BY state ASC LIMIT 0, 20");
echo "<table border='1'> 
<tr> 
<th>Company Name</th> 
<th>State</th> 
<th>Phone</th> 
<th>Contact First Name</th> 
<th>Contact Last Name</th> 
<th>Title</th> 
<th></th>
<th>Status</th>
</tr>"; 
while($row = mysql_fetch_array($result)) 
{ 
echo "<tr>"; 
echo "<td>" . $row['company_name'] . "</td>"; 
echo "<td>" . $row['state'] . "</td>"; 
echo "<td>" . $row['phone'] . "</td>"; 
echo "<td>" . $row['contact_first'] . "</td>"; 
echo "<td>" . $row['contact_last'] . "</td>"; 
echo "<td>" . $row['title'] . "</td>"; 
echo "<td>" . $row['status'] . "</td>"; 

echo "<td><form name=\"status\" action=\"updateDB.php\" method=\"post\">
<input type='hidden' name='entry_row' value='".$row['id']."' />
<select>
<option value=Call_Me_selected>Call Me!</option>
<option value=Called-No Message>Called-No Message</option>
<option value=Called-Left Message>Called-Left Message</option>
<option value=Called-No Interest>Called-No Interest</option>
<option value=Prospect Call Back>Prospect Call Back</option>
<option value=Prospect Analysis>Prospect Analysis</option>
<option value=Prospect Quote>Prospect Quote</option>
<option value=Do Not Call>Do Not Call</option>

</select><input type=\"submit\" value=\"Submit status\" /></form><br/><br/>row:" . $row['id'] . "</td>\n"; 

echo "</tr>"; 
} 
echo "</table>"; 
//mysql_close($con); 
?>

updateDB.php

<?php
$hostname="";
$username="";
$password="";
$db="";

$entry= $_POST['status'];
$entry_row=$_POST['entry_row'];
 
// Connect to Mysql
$global_dbh = mysql_connect($hostname, $username, $password)
or die("Could not connect to database");
//Select mysql database
mysql_select_db($db, $global_dbh)
or die("Could not select database");
 
$update=mysql_query("UPDATE cold_call_data SET status = $entry
WHERE id = $entry_row ")
or die("Could not update database!");
echo $update; 
echo "\ndatabase updated successfully!";
 
?>

Hi pickett65,

Check the value of $entry in your updateDB.php page. This value isn't being sent through from your form.

Zagga

[pickett65]

entry should be sent from the value of $_POST.

You have no form value for 'status' being sent through, you forgot to name your <select> tag in the form.

<select name='status'>

[pickett65]

i see said the blind man as he picked up his hammer and saw...

totally makes sense.

So does the form need a name or can i just take the form name away and add the name to the select input?

I tend to name all my form fields so I would keep it, but in this instance it doesn't matter what you call it. The name only really matters when you have multiple forms or use some javascript trickery on it.

[faroukmuhammad]

The Select tag as no name, so i added the name "status" (see also line 36) to is, furthermore the values for the optoins need to be in qoutes, remember(line 37-44). Below is the new display_table.php

display_table.php

<?php
$hostname="";
$username="";
$password="";
$db="";

$global_dbh = mysql_connect($hostname, $username, $password)or die("Could not connect to database");
mysql_select_db($db, $global_dbh) or die("Could not select database");


$result = mysql_query("SELECT id, state, company_name, phone, contact_first, contact_last, status, title FROM cold_call_data ORDER BY state ASC LIMIT 0, 20");
echo "<table border='1'> 
<tr> 
<th>Company Name</th> 
<th>State</th> 
<th>Phone</th> 
<th>Contact First Name</th> 
<th>Contact Last Name</th> 
<th>Title</th> 
<th></th>
<th>Status</th>
</tr>"; 
while($row = mysql_fetch_array($result)) 
{ 
echo "<tr><form name=\"status\" action=\"updateDB.php\" method=\"post\">"; 
echo "<td>" . $row['company_name'] . "</td>"; 
echo "<td>" . $row['state'] . "</td>"; 
echo "<td>" . $row['phone'] . "</td>"; 
echo "<td>" . $row['contact_first'] . "</td>"; 
echo "<td>" . $row['contact_last'] . "</td>"; 
echo "<td>" . $row['title'] . "</td>"; 
echo "<td>" . $row['status'] . "</td>"; 

echo "<td>
<input type='hidden' name='entry_row' value='".$row['id']."' />
<select name = 'status'>
<option value= \"Call_Me_selected\">Call Me!</option>
<option value= \"Called-No Message\">Called-No Message</option>
<option value= \"Called-Left Message\">Called-Left Message</option>
<option value= \"Called-No Interest\">Called-No Interest</option>
<option value= \"Prospect Call Back\">Prospect Call Back</option>
<option value= \"Prospect Analysis\">Prospect Analysis</option>
<option value= \"Prospect Quote\">Prospect Quote</option>
<option value= \"Do Not Call\">Do Not Call</option>

</select><input type=\"submit\" value=\"Submit\" /></form><br/><br/>row:" . $row['id'] . "</td>\n"; 

echo "</tr>"; 
} 
echo "</table>"; 
//mysql_close($con); 
?>

With respect to updateDB.php, mysql_query has no connection id(line 19) and status = $entry should be status = $entry (line 18) as show in the code below.

updateDB.php

<?php
$hostname="";
$username="";
$password="";
$db=""; 

$entry= $_POST['status'];
$entry_row=$_POST['entry_row']; // Connect to Mysql


$global_dbh = mysql_connect($hostname, $username, $password) or die("Could not connect to database");


//Select mysql database

mysql_select_db($db, $global_dbh) or die("Could not select database");

$query = "UPDATE cold_call_data SET status = '$entry' WHERE id = $entry_row ";
$update = mysql_query($query, $global_dbh) or die("Could not update database!");

echo $update; 

echo "\ndatabase updated successfully!"; 
?>

[pickett65]

faroukmuhummad was right with the quotes. just forgot to add them to $entry_row also. thanks for that!
script is running as planned!

Thanks for all that helped!