Updating a MySQL Database via PHP

Question

fattpacman 0 Newbie Poster

12 Years Ago

Hi,

I've just switched web hosts, and I have this script that ultimately connects to a database and displays it on a webpage.

It used to work fine, however now I can get it to display the database info in the webpage, however I can't update the fields. I can create new fields within some tables of the database, yet doing anything more than that, and it's not working - no error message, the page just seems to refresh and that's that.

I was wondering if an expert could have any suggestions of things to check, as I can't figure this one out - I've changed the config files to match the new DB, which is what I just can't understand.

Any help would be appreciated, and if needed I can post the code and stuff.

Thanks,

Ryan

mysql php

2 Contributors
7 Replies
290 Views
19 Hours Discussion Span
Latest Post 12 Years Ago Latest Post by diafol

diafol

12 Years Ago

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.

diafol

12 Years Ago

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

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

fattpacman 0 Newbie Poster · Answer 1 · 2012-11-20T00:12:24+00:00

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.

fattpacman 0 Newbie Poster · Answer 2 · 2012-11-20T17:02:08+00:00

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).

diafol · Answer 3 · 2012-11-20T17:29:50+00:00

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.

fattpacman 0 Newbie Poster · Answer 4 · 2012-11-20T18:15:12+00:00

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)

diafol · Answer 5 · 2012-11-20T18:48:48+00:00

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.