Updating Database Tables

vicdesigns · July 2, 2011

Howdy.

I am trying to update Database Tables from a form. Obviously doesn't work. I have tried to follow a few tuts on the web but alas, nothing.

Here is what the tuts suggested:

<?php $SQL = "UPDATE settings SET email = '$email';
SET site_title = '$site_title'"; ?>

All I want to do for now is update the settings table as above. But nothing I try works. I keep getting errors saying that email and site_title are undefined. Yet, they work fine as these values are displayed on the site from the database.

Any ideas?

Pikachu2000 · July 2, 2011

You'll want to add a WHERE clause to that query, unless you actually want every record in the table changed to the same values, that is.

<?php $SQL = "UPDATE settings SET email = '$email', site_title = '$site_title'"; ?>

vicdesigns · July 2, 2011

I tried with the following but it didn't work either:

$SQL = "UPDATE settings SET site_title = '$site_title' WHERE email = '$email'";

Gave the same errors.

wildteen88 · July 2, 2011

Gave the same errors.

What errors? Where are the variables $site_title and $email defined?

vicdesigns · July 2, 2011

I have these set in the database tables already and displaying them on the site with, for example:

<?php echo $row['site_title']; ?>

Now, I am guessing I am doing everything butt ways. But, I really need to get these working.

Errs:

Notice: Undefined variable: site_title in C:\wamp\www\site\admin\configuration.php on line 158

Any help in the right direction, without too much jargon is greatly appreciated.

wildteen88 · July 2, 2011

$row['site_title'] is not the same as $site_title.

Can you explain what it is you're trying to do.

EdwinPaul · July 2, 2011

I tried with the following but it didn't work either:
$SQL = "UPDATE settings SET site_title = '$site_title' WHERE email = '$email'";
Gave the same errors.

If you use the above query, you say: "I want to update the field 'site_title' from a row in my tabel, where the table-field 'email' is equal to the contents of my own variable '$email', with the contents of my own variable '$site_title'.

It is confusing to give your own variables the same name as the fields in the row of your database. Are you sure your own variables $site_title and $email exist ?

Alkimuz · July 2, 2011

just checking, but you do add the codes to run this SQL right? like:

$con = mysql_connect($host, $user, $password);
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
$db = mysql_select_db($database, $con);

$SQL = "UPDATE settings SET site_title = '$site_title' WHERE email = '$email'";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }

vicdesigns · July 3, 2011

Hi guys,

Okay, this is what I am trying to do.

I want to submit a form that will update a table's columns in MySQL with new changes.

So, if someone changes the Site Name, it will update the row site_name with the new data.

To display the site name on the front end, I am using the following:

<?php echo $row['site_name']; ?>

Here is what I have in order to try and update the database:

$con = mysql_connect($dbhost, $dbuser, $dbpass);
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
$db = mysql_select_db($dbname, $con);

$SQL = "UPDATE settings SET site_title = '$site_title' WHERE email = '$email'";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }

When the form is submitted I now get "Error: Query was empty" along with the following:

Notice: Undefined variable: site_title in C:\wamp\www\site\admin\configuration.php on line 12

I am at a complete loss here as I am unable to understand what it means by "Undefined Variable". I am just learning.

Any guidance would be grateful.

EdwinPaul · July 3, 2011

About the query: names of variables are CASE-SENSITIVE. You name your query $SQL but you use it as $sql. Change that.

About the 'site_title': You are trying to update the table-field 'site_title' with the contents of your variable $site_title. That last variable $site_title has to be filled from your form BEFORE you can use it. All the fields of a form are in an array called $_POST, so if you want to use one of your form-fields, you will have to address it like $_POST['site_title'].

Examine your form and find out how that field is named.

vicdesigns · July 3, 2011

Howdy.

Have those issues sorted now.

$site_title = isSet($_REQUEST['site_title']) ? $_REQUEST['site_title'] : '';
$site_slogan = isSet($_REQUEST['site_slogan']) ? $_REQUEST['site_slogan'] : '';
$footer_text = isSet($_REQUEST['footer_text']) ? $_REQUEST['footer_text'] : '';
$keywords = isSet($_REQUEST['keywords']) ? $_REQUEST['keywords'] : '';
$email = isSet($_REQUEST['email']) ? $_REQUEST['email'] : '';
$first_name = isSet($_REQUEST['first_name']) ? $_REQUEST['first_name'] : '';
$last_name = isSet($_REQUEST['last_name']) ? $_REQUEST['last_name'] : '';
$con = mysql_connect($dbhost, $dbuser, $dbpass);
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
$db = mysql_select_db($dbname, $con);

$query = "UPDATE settings SET site_slogan = '$site_slogan' WHERE site_title = 'site_title'";

if (!mysql_query($query,$con))
  {
  die('Error: ' . mysql_error());
  }

The form:

											<form name="config" method="POST" action="">
										<table width="90%" border="0" cellspacing="0" cellpadding="0">
										<tr>
										<td class="label">Site Name:</td> <td><input class="conf-input" name="site_title" id="site_title" type="text" size="35" value="<?php echo $site_title; ?>"></td>
										</tr>
										<tr>
										<td class="label">Site Slogan:</td> <td><input class="conf-input" name="site_slogan" type="text" size="35" value="<?php echo $site_slogan; ?>"></td>
										</tr>
										<tr>
										<td class="label">Site Keywords:</td> <td><input class="conf-input" name="keywords" type="text" size="35" value="<?php echo $keywords; ?>"></td>
										</tr>
										<tr>
										<td class="label">Your First Name:</td> <td><input class="conf-input" name="first_name" id="first_name" type="text" size="35" value="<?php echo $first_name; ?>"></td>
										<tr>
										<td class="label">Your Last Name:</td> <td><input class="conf-input" name="last_name" id="last_name" type="text" size="35" value="<?php echo $last_name; ?>"></td>
										<tr>
										<td class="label">Your Email:</td> <td><input class="conf-input" name="email" id="email" type="text" size="35" value="<?php echo $email; ?>"></td>
										</tr>
										<tr>
										<td class="label">Footer Text:</td> <td><textarea class="conf-input" name="footer_text" rows="5" cols="50"><?php echo $footer_text; ?></textarea>
										</td>
										</tr>
										<tr>
										<td><input type="submit" value="Save"></td>
										</tr>
										</table>
										</form></p>

The form updates fine and the variables are kept in the form fields. Only issue is, it is not updating the database. I will keep hammering at it, but if anyone can see where I am screwing up, that would be great.

wildteen88 · July 3, 2011

site_title = 'site_title' most probably needs to be site_title = '$site_title' on this line

$query = "UPDATE settings SET site_slogan = '$site_slogan' WHERE site_title = 'site_title'";

EdwinPaul · July 3, 2011

The first part needs some changing:

/* $_REQUEST should not be used. It is a combination of $_GET, $_POST and $_COOKIE so you will not
   know where your variable is coming from  */
/* isSet should be: isset
// Change the first part to:
$site_title = isset($_POST['site_title']) ? $_POST['site_title'] : '';
$site_slogan = isset($_POST['site_slogan']) ? $_POST['site_slogan'] : '';
$footer_text = isset($_POST['footer_text']) ? $_POST['footer_text'] : '';
$keywords = isset($_POST['keywords']) ? $_POST['keywords'] : '';
$email = isset($_POST['email']) ? $_POST['email'] : '';
$first_name = isset($_POST['first_name']) ? $_POST['first_name'] : '';
$last_name = isset($_POST['last_name']) ? $_POST['last_name'] : '';

in your query you forgot the $-sign :

$query = "UPDATE settings SET site_slogan = '$site_slogan' WHERE site_title = 'site_title'";      //  '$site_title'";

[edit]

sorry, wildteen, it took me a while so you beat me to it... ;-)

vicdesigns · July 3, 2011

The first part needs some changing:

/* $_REQUEST should not be used. It is a combination of $_GET, $_POST and $_COOKIE so you will not
   know where your variable is coming from  */
/* isSet should be: isset
// Change the first part to:
$site_title = isset($_POST['site_title']) ? $_POST['site_title'] : '';
$site_slogan = isset($_POST['site_slogan']) ? $_POST['site_slogan'] : '';
$footer_text = isset($_POST['footer_text']) ? $_POST['footer_text'] : '';
$keywords = isset($_POST['keywords']) ? $_POST['keywords'] : '';
$email = isset($_POST['email']) ? $_POST['email'] : '';
$first_name = isset($_POST['first_name']) ? $_POST['first_name'] : '';
$last_name = isset($_POST['last_name']) ? $_POST['last_name'] : '';

in your query you forgot the $-sign :

$query = "UPDATE settings SET site_slogan = '$site_slogan' WHERE site_title = 'site_title'";      //  '$site_title'";

[edit]

sorry, wildteen, it took me a while so you beat me to it... ;-)

Thank you mate, you are a Gem. However, it is still not working. I think there is something with the form, as I now see, after clearing browser cache etc, the form is not staying updated. All the fields go back blank. Will still fire away with it.

EdwinPaul · July 3, 2011

The first time you display your form, the variables are not filled yet.

<td class="label">Site Name:</td> <td><input class="conf-input" name="site_title" id="site_title" type="text" size="35" value="<?php echo $site_title; ?>"></td>

All those lines of your form should be changed to:

<td class="label">Site Name:</td> <td><input class="conf-input" name="site_title" id="site_title" type="text" size="35" value="<?php echo isset($site_title) ? $site_title : '' ; ?>"></td>

vicdesigns · July 3, 2011

Thank you. Your help is really appreciated. At least you are explaining what changes need to be made, which is helping me understand what each thing is about.

But I need to upset you because that didn't work either. Still the same. *runs and hides.

EdwinPaul · July 3, 2011

Maybe I can help you better if you post the WHOLE script. Please replace passwords etc by *** ;-)

vicdesigns · July 3, 2011

Maybe I can help you better if you post the WHOLE script. Please replace passwords etc by *** ;-)

If you can help I will definitely give you a mention in the script comments.

I won't post it all here, but if you inbox me your email I will zip it off to you.

vicdesigns · July 5, 2011

I am still at a complete loss with this.

There are two major issues:

1. The form will not display what's in the database

2. The form will not update the database

Here is the code:

<?php
include_once'../includes/english.php';
include_once'../includes/config.php';

// Set the Variables as Defined
$site_title = isset($_POST['site_title']) ? $_POST['site_title'] : '';
$site_slogan = isset($_POST['site_slogan']) ? $_POST['site_slogan'] : '';
$footer_text = isset($_POST['footer_text']) ? $_POST['footer_text'] : '';
$keywords = isset($_POST['keywords']) ? $_POST['keywords'] : '';
$email = isset($_POST['email']) ? $_POST['email'] : '';
$first_name = isset($_POST['first_name']) ? $_POST['first_name'] : '';
$last_name = isset($_POST['last_name']) ? $_POST['last_name'] : '';
$con = mysql_connect($dbhost, $dbuser, $dbpass);
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
$db = mysql_select_db($dbname, $con);

// Get all the data from the tables
$qry = "SELECT site_title, site_slogan, footer_text FROM settings";
$qry = "UPDATE settings SET site_slogan = '$site_slogan' WHERE site_title = '$site_title'";

if (!mysql_query($qry,$con))
  {
  die('Error: ' . mysql_error());
  }
?>

And the form:

<p>

<form method="post" action="">
<table width="90%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="label">Site Name:</td> <td><input class="conf-input" name="site_title" id="site_title" type="text" size="35" value="<?php echo isset($_POST['site_title']) ? $_POST['site_title'] : '';  ?>"></td>
</tr>
<tr>
<td class="label">Site Slogan:</td> <td><input class="conf-input" name="site_slogan" id="site_slogan" type="text" size="35" value="<?php echo isset($_POST['site_slogan']) ? $_POST['site_slogan'] : '';  ?>"></td>
</tr>
<tr>
<td class="label">Footer Text:</td> <td><textarea class="conf-input" name="footer_text" id="footer_text" rows="5" cols="50"><?php echo isset($_POST['footer_text']) ? $_POST['footer_text'] : '';  ?></textarea>
</td>
</tr>
<tr>
<td><input name="submit" id="submit" type="submit" value="Save"></td>
</tr>
</table>
</form></p>

EdwinPaul has been excellent and very patient. But, he said himself that the way I had it was a mess, which I agree. I since then ripped all the php out and re-added it using EdwinPaul's suggestions etc, and following tutorials. I am unable to figure out why the two issues are being caused. It has me baffled.

Any help on this is greatly appreciated.

Adam · July 5, 2011

1. You're not actually querying the database for the data? You're missing out a step entirely. Read up on the mysql_fetch_* functions (mysql_fetch_assoc for example). Also when you define your two SQL statements, you're overwriting the first one with the second:

$qry = "SELECT site_title, site_slogan, footer_text FROM settings";
$qry = "UPDATE settings SET site_slogan = '$site_slogan' WHERE site_title = '$site_title'";

You need to use a different variable for each and query them separately.

2. mysql_query returns true/false on INSERT/UPDATE/etc type statements, indicating whether the query was successfully executed. A true return here though does not mean any rows in the table were actually affected by the statement, just that it was executed okay -- which is what I think the case is here. Generally false will only ever be returned if there's an error.

What you should use is (not necessarily to the letter of course):

$result = mysql_query($qry,$con) or trigger_error('MySQL error: ' . mysql_error(), E_USER_ERROR);

That will execute the function, and if it returns false trigger an error. Using trigger_error is a good habit to get into it, as you can customise the level of errors to report later on -- think about production environments where you don't want PHP errors to be shown to the user, but you do in development.

Remember though that will only indicate that the query was executed successfully. You can check how many rows were affected using mysql_affected_rows. If it's 0 you know you have a logic problem, but if it's more you know it's updated something (which your WHERE condition should guarantee you update the correct row(s)). If you get an error triggered, you know it's a syntax problem.

Looking at your code though I'm a little unsure of the flow; when should you update the row and when should you select?

Sign In

Updating Database Tables

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

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