Updating a MySQL Database via PHP

Newbie Poster

4 posts since Nov 2012

Reputation Points: 0

Solved Threads: 0

Skill Endorsements: 0

0

I'm assuming you just have the one location for setting connection details. If you have them all over the place, ensure that you've changed them all. However, you'd probably get an error if you've used the 'or die(...)' structure.

Do you have the right priviledges (for the user mentioned in the connection details) to do what you need?

It may be useful to give an example of any code that fails to work. Scramble your connection details (user/pw) before posting though.

Keep Smiling

Moderator

10,672 posts since Oct 2006

Reputation Points: 1,632

Solved Threads: 1,514

Skill Endorsements: 57

0

I've copied/pasted the page to where you make the majority of the changes. Apologies if it's a little messy!

Also, I don't know why or even how, but on PHPMyAdmin, I'm looking at the database, and when I click on the 'schedule' table, it just logs me out. Every time. I've tried deleting the database and restoring it, but it used to work. I really don't understand...

<?php

Header('Cache-Control: no-cache');
Header('Pragma: no-cache');

include_once("config.php");

db_connect($config['dbserver'], $config['username'], $config['password'], $config['database']);

if ($_GET["day"]!="") $day = $_GET["day"];

$bgcolor = "#DDDDFF";

?>

<html>
<head>

<meta http-equiv="Expires" content="Tue, 01 Jan 2000 12:12:12 GMT" />
<meta http-equiv="Pragma" content="no-cache" />

<script type="text/javascript">
<!--
function confirmPost()
{
var agree=confirm("Are you sure you want to delete this item?");
if (agree)
return true ;
else
return false ;
}
// -->
</script>

</head>
<body>

<?php include('./includes/header.php'); ?>

<table border="0" width="1000" cellspacing="0" cellpadding="0">
<tr><td><b>Start</b></td><td><b>End</b></td><td><b>Presenter</b></td><td><b>Show</b></td><td><b>Description</b></td><td><b>Studio</b></td><td><b>Type</b></td><td><b>Update</b></td><td><b>Options</b></td></tr>

<?php

$query = "SELECT * FROM " . $dbtable . " WHERE `day` = '" . $day . "' ORDER BY `time_start` ASC";

$result = mysql_query( $query );

while($row = mysql_fetch_array($result))
  {

$show = $row['show'];
$description = $row['description'];
$studio = $row['studio'];
$ptype = $row['ptype'];
$longdesc = $row['longdesc'];
$time_start = $row['time_start'];
$time_end = $row['time_end'];

echo "
<form name='schedule' action='update.php' method='post' ></form>
<tr><td bgcolor='$bgcolor'>
";
$ident = $row['id'];
echo "
<input type='hidden' name='day' value='". $day ."' />
<input type='hidden' name='id' value='". $ident ."' />
<input type='hidden' name='returnto' value='index' />

<input type='text' name='time_start' size='4' value='". $time_start ."' />
</td>

<td bgcolor='$bgcolor'>
<input type='text' name='time_end' size='4' value='". $time_end ."' />
</td>

<td bgcolor='$bgcolor'>
";

select_presenter($row['presenter']);

echo "
</td>
<td bgcolor='$bgcolor'>
";
// <input type='text' name='show' value='". $show ."'>
select_show($row['show_id']);
echo "
</td>
<td bgcolor='$bgcolor'>
<input type='text' name='desc' size='60' value='" . $description . "'>
</td>
<td bgcolor='$bgcolor'>

<select  name='studio' size='1'>
<option value=''></option>
<option value='E1'";
if ($studio=='E1') echo 'selected';
echo ">E1</option>

<option value='E3'";
if ($studio=='E3') echo 'selected';
echo ">E3</option>

<option value='E4'";
if ($studio=='E4') echo 'selected';
echo ">E4</option>

<option value='K1'";
if ($studio=='K1') echo 'selected';
echo ">K1</option>

<option value='SL'";
if ($studio=='SL') echo 'selected';
echo ">SL</option>

<option value='OB'";
if ($studio=='OB') echo 'selected';
echo ">OB</option>

<option value='NA'";
if ($studio=='NA') echo 'selected';
echo ">NA</option>
</select>

</td>
<td bgcolor='$bgcolor'>

<select  name='ptype' size='1'>
<option value='A'";
if ($ptype=='A') echo 'selected';
echo ">Audio</option>
<option value='V'";
if ($ptype=='V') echo 'selected';
echo ">Video</option>
</select>

</td>

<td bgcolor='$bgcolor'>
<input type='submit' value='Update'>
</td>

<td bgcolor='$bgcolor'>
<a href='edit.php?edit=" . $ident . "'><img src='./img/edit.png'></a> <a href='delete.php?id=" . $ident . "' onClick='return confirmPost()'><img src='./img/del.png'></a>
</td>

</tr>

<tr><td colspan='5' bgcolor='$bgcolor'>
<input type='text' name='longdesc' size='160' value='" . $longdesc . "'>
</td>

<td colspan='4' bgcolor='$bgcolor'>

</td>


</tr>

<tr height='10'></tr>

</form>
";
  }

?>

</table>

<a href="add.php?day=<?php echo $day; ?>">add line</a>

<?php include('./includes/footer.php'); ?>

</body>
</html>

I just find it a little strange because it worked on the old host, but not now. I'm wondering if the DB has a setting where it allows changes without having it actually open, or something like that? The user for the DB has full privelledges, so I doubt that's the problem.

Cheers.

Newbie Poster

4 posts since Nov 2012

Reputation Points: 0

Solved Threads: 0

Skill Endorsements: 0

0

OK, before looking at the code. phpMyAdmin logs you out as soon as you press schedule. That sounds odd. Have you contacted your host?

Keep Smiling

Moderator

10,672 posts since Oct 2006

Reputation Points: 1,632

Solved Threads: 1,514

Skill Endorsements: 57

0

Yeah, I opened a ticket, and they found nothing wrong. I tried again and it allowed me to access the table again, however I still can't update the database via the webpage (the code I posted earlier).

Newbie Poster

4 posts since Nov 2012

Reputation Points: 0

Solved Threads: 0

Skill Endorsements: 0

0

echo your query to the screen:

$query = "SELECT * FROM " . $dbtable . " WHERE `day` = '" . $day . "' ORDER BY `time_start` ASC";
echo $query;
exit;

The exit will stop further execution (as you know). COpy the text from the screen and paste into the sql pane in phpmyadmin. Run the query and see if it gets updated or if nothing happens.

Keep Smiling

Moderator

10,672 posts since Oct 2006

Reputation Points: 1,632

Solved Threads: 1,514

Skill Endorsements: 57

0

I'm not sure if I follow.

I copied the text you gave into the sql pane, and it came up with an error message:

1064 - 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 '$query = "SELECT * FROM " . $dbtable . " WHERE day = '" . $day . "' ORDER BY `' at line 1

I'm assuming that's what you told me to do? (I have a basic, self taught knowledge, the person that created this script for me isn't able to help me diagnose the problem)

Newbie Poster

4 posts since Nov 2012

Reputation Points: 0

Solved Threads: 0

Skill Endorsements: 0

0

No, highlight the output from the screen (from the code I supplied - or modified), copy it and paste that into the sql window. You shouldn't see any php in it.