arrrgghhh - php & mysql not playing nice together..!
Moderator: General Moderators
- DiamondLil
- Forum Newbie
- Posts: 15
- Joined: Fri Oct 04, 2002 11:14 am
arrrgghhh - php & mysql not playing nice together..!
Hi guys - I could use some help.
Here's the situation: I've got 10 php pages:
add.php, delete.php, edit.php, footer.php (just html footer), header.php (just html head), index.php (as a default will just list the entries of the db on server), list.php, save.php, update.php, and vars.php. (holds the variables for the database connection, & name, & table name).
The pages' name pretty much indicates its function.
Here's the link : http://www.mc2ads.com/prologi/editbox/index.php
I have gone through each of the pages, I'm not finding any stray characters. I've deleted and re-typed certain pieces.
I can add records so I know that I'm connecting & that the variables are ok, but when I click on "edit" on index.php, it does not grab the values of the cells and plug them into the form fields for the edit.php pg.
When I hit "edit", I'm getting : Warning: Supplied argument is not a valid MySQL result resource in usr/local/etc/httpd/htdocs/prologi/editbox/edit.php on line 5.
Here are the first 6 lines of edit.php:
<?
mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name);
$result = mysql_query("SELECT * FROM $db_table WHERE id=$editid");
$edit = mysql_fetch_array($result);
?>
Also, the "delete" link isn't working. It looks like it is reading delete.php and refreshing the index.php pg, but it's not deleting the info. It's not giving me an error message.
I'm editing the pages in SimpleText so I know that it's not a GoLive wysiwyg messing with the code thing.
Any ideas?
I'll post all of the code for all of the pages and maybe someone else can see what I'm just not getting because I'm about to go completely postal.
----------------------------------------------------------------------------------
add.php
----------------------------------------------------------------------------------
<form method="post" action="index.php">
<input type="hidden" name="todo" value="save">
<table>
<tr>
<td>Load Date:</td>
<td><input type="text" name="loaddate"></td>
</tr>
<tr>
<td>Originating City:</td>
<td><input type="text" name="origcity"></td>
</tr>
<tr>
<td>Originating State:</td>
<td><input type="text" name="origstate"></td>
</tr>
<tr>
<td>Destination City:</td>
<td><input type="text" name="destcity"></td>
</tr>
<tr>
<td>Destination State:</td>
<td><input type="text" name="deststate"></td>
</tr>
<tr>
<td></td>
<td><input type="submit" name="submit" value="add"></td>
</tr>
</table>
</form>
----------------------------------------------------------------------------------
delete.php
----------------------------------------------------------------------------------
<?
mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name);
$result = mysql_query("DELETE FROM $db_table WHERE id=$deleteid");
?>
----------------------------------------------------------------------------------
edit.php
----------------------------------------------------------------------------------
<?
mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name);
$result = mysql_query("SELECT * FROM $db_table WHERE id=$editid");
$edit = mysql_fetch_array($result);
?>
<form method="post" action="index.php">
<input type="hidden" name="todo" value="update">
<input type="hidden" name="id" value="<? echo $edit["id"] ?>">
<table>
<tr>
<td>Load Date:</td>
<td><input type="text" name="loaddate" value="<? print $edit["loaddate"] ?>"></td>
</tr>
<tr>
<td>Originating City:</td>
<td><input type="text" name="origcity" value="<? print $edit["origcity"] ?>"></td>
</tr>
<tr>
<td>Originating State:</td>
<td><input type="text" name="origstate" value="<? print $edit["origstate"] ?>"></td>
</tr>
<tr>
<td>Destination City:</td>
<td><input type="text" name="destcity" value="<? print $edit["destcity"] ?>"></td>
</tr>
<tr>
<td>Destination State:</td>
<td><input type="text" name="deststate" value="<? print $edit["deststate"] ?>"></td>
</tr>
<tr>
<td></td>
<td><input type="submit" name="submit" value="update"></td>
</tr>
</table>
</form>
----------------------------------------------------------------------------------
footer.php
----------------------------------------------------------------------------------
</BODY>
</HTML>
----------------------------------------------------------------------------------
header.php
----------------------------------------------------------------------------------
<HTML>
<HEAD>
<TITLE>
<?
echo "$title";
?>
</TITLE>
</HEAD>
<BODY>
----------------------------------------------------------------------------------
index.php
----------------------------------------------------------------------------------
<?
require("vars.php"); //for database connection vars
switch ($todo) {
case "add":
//dispaly a blank form
$title="Add new entry"; //used by header
require("header.php"); // your page header
require("add.php"); // submits to index.php?todo=save
require("footer.php"); // your page footer
break;
case "edit":
// pull the data from db and plugs into form
$title = "Edit Existing Entry"; // used by header
require("header.php"); // your page header
require("edit.php"); // edits $editid, submits to index.php?todo=update
require("footer.php"); // your page footer
break;
case "delete":
require("delete.php"); // deletes $deleteid from the db
header("Location: index.php"); // go to listing
break;
case "save":
require("save.php"); // add new entry to db
header("Location: index.php"); // go to listing
break;
case "update":
require("update.php"); // update the db where id=$id
header("Location: index.php"); // go to listing
break;
default:
$title = "Viewing All Entries"; // used by header
require("header.php"); // your page header
require("list.php"); //show all entries
require("footer.php"); // your page footer
break;
}
?>
----------------------------------------------------------------------------------
list.php
----------------------------------------------------------------------------------
<link href="style.css" rel="stylesheet" media="screen">
<div align="center">
<span class="headers">Test Database Template</span>
<p>
<span class="bodystyle"><a href="index.php?todo=add">Add a Record</a></span><br>
<br>
</p>
<table width="650" cellspacing="2" border="0" cellpadding="1">
<tr bgcolor="#DADADA" align="center">
<td>ID</td>
<td>Load Date</td>
<td>Originating City</td>
<td>Originating State</td>
<td>Destination City</td>
<td>Destination State</td>
<td>Delete</td>
<td>Edit</td>
</tr>
<?php
mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name);
$result = mysql_query("select * from $db_table");
while($r=mysql_fetch_array($result))
{
$id=$r['id'];
$loaddate=$r['loaddate'];
$origcity=$r['origcity'];
$origstate=$r['origstate'];
$destcity=$r['destcity'];
$deststate=$r['deststate'];
print '
<tr>
<td>' . $id . '</td>
<td>' . $loaddate . '</td>
<td>' . $origcity . '</td>
<td>' . $origstate . '</td>
<td>' . $destcity . '</td>
<td>' . $deststate . '</td>
<td><a href="mailto:' . $email . '">' . $email . '</a></td>
<td><a href="index.php?todo=delete&deleteid=' . $id . '">delete</td>
<td><a href="index.php?todo=edit&editid=' . $id . '">edit</td>
</tr>';
}
?>
</table>
<br>
<span class="bodystyle"> <a href="#top">Top of Page</a> </span>
</div>
----------------------------------------------------------------------------------
save.php
----------------------------------------------------------------------------------
<?
mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name);
$result = mysql_query("INSERT INTO $db_table (id,loaddate,origcity,origstate,destcity,deststate) VALUES ('NULL', '$loaddate', '$origcity', '$origstate', '$destcity', '$deststate')");
?>
----------------------------------------------------------------------------------
update.php
----------------------------------------------------------------------------------
<?
mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name);
$result = mysql_query("UPDATE $db_table SET loaddate=$loaddate,origcity=$origcity,origstate=$origstate,destcity=$destcity,deststate=$deststate WHERE id=$id");
?>
----------------------------------------------------------------------------------
vars.php
----------------------------------------------------------------------------------
This page just holds the actual variables, which I can't post for obvious security reasons but we have already established that all of these are correct and working because we can connect and add records using the variable names.
I hope I haven't forgotten anything...oh yeah - HELP!
Here's the situation: I've got 10 php pages:
add.php, delete.php, edit.php, footer.php (just html footer), header.php (just html head), index.php (as a default will just list the entries of the db on server), list.php, save.php, update.php, and vars.php. (holds the variables for the database connection, & name, & table name).
The pages' name pretty much indicates its function.
Here's the link : http://www.mc2ads.com/prologi/editbox/index.php
I have gone through each of the pages, I'm not finding any stray characters. I've deleted and re-typed certain pieces.
I can add records so I know that I'm connecting & that the variables are ok, but when I click on "edit" on index.php, it does not grab the values of the cells and plug them into the form fields for the edit.php pg.
When I hit "edit", I'm getting : Warning: Supplied argument is not a valid MySQL result resource in usr/local/etc/httpd/htdocs/prologi/editbox/edit.php on line 5.
Here are the first 6 lines of edit.php:
<?
mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name);
$result = mysql_query("SELECT * FROM $db_table WHERE id=$editid");
$edit = mysql_fetch_array($result);
?>
Also, the "delete" link isn't working. It looks like it is reading delete.php and refreshing the index.php pg, but it's not deleting the info. It's not giving me an error message.
I'm editing the pages in SimpleText so I know that it's not a GoLive wysiwyg messing with the code thing.
Any ideas?
I'll post all of the code for all of the pages and maybe someone else can see what I'm just not getting because I'm about to go completely postal.
----------------------------------------------------------------------------------
add.php
----------------------------------------------------------------------------------
<form method="post" action="index.php">
<input type="hidden" name="todo" value="save">
<table>
<tr>
<td>Load Date:</td>
<td><input type="text" name="loaddate"></td>
</tr>
<tr>
<td>Originating City:</td>
<td><input type="text" name="origcity"></td>
</tr>
<tr>
<td>Originating State:</td>
<td><input type="text" name="origstate"></td>
</tr>
<tr>
<td>Destination City:</td>
<td><input type="text" name="destcity"></td>
</tr>
<tr>
<td>Destination State:</td>
<td><input type="text" name="deststate"></td>
</tr>
<tr>
<td></td>
<td><input type="submit" name="submit" value="add"></td>
</tr>
</table>
</form>
----------------------------------------------------------------------------------
delete.php
----------------------------------------------------------------------------------
<?
mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name);
$result = mysql_query("DELETE FROM $db_table WHERE id=$deleteid");
?>
----------------------------------------------------------------------------------
edit.php
----------------------------------------------------------------------------------
<?
mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name);
$result = mysql_query("SELECT * FROM $db_table WHERE id=$editid");
$edit = mysql_fetch_array($result);
?>
<form method="post" action="index.php">
<input type="hidden" name="todo" value="update">
<input type="hidden" name="id" value="<? echo $edit["id"] ?>">
<table>
<tr>
<td>Load Date:</td>
<td><input type="text" name="loaddate" value="<? print $edit["loaddate"] ?>"></td>
</tr>
<tr>
<td>Originating City:</td>
<td><input type="text" name="origcity" value="<? print $edit["origcity"] ?>"></td>
</tr>
<tr>
<td>Originating State:</td>
<td><input type="text" name="origstate" value="<? print $edit["origstate"] ?>"></td>
</tr>
<tr>
<td>Destination City:</td>
<td><input type="text" name="destcity" value="<? print $edit["destcity"] ?>"></td>
</tr>
<tr>
<td>Destination State:</td>
<td><input type="text" name="deststate" value="<? print $edit["deststate"] ?>"></td>
</tr>
<tr>
<td></td>
<td><input type="submit" name="submit" value="update"></td>
</tr>
</table>
</form>
----------------------------------------------------------------------------------
footer.php
----------------------------------------------------------------------------------
</BODY>
</HTML>
----------------------------------------------------------------------------------
header.php
----------------------------------------------------------------------------------
<HTML>
<HEAD>
<TITLE>
<?
echo "$title";
?>
</TITLE>
</HEAD>
<BODY>
----------------------------------------------------------------------------------
index.php
----------------------------------------------------------------------------------
<?
require("vars.php"); //for database connection vars
switch ($todo) {
case "add":
//dispaly a blank form
$title="Add new entry"; //used by header
require("header.php"); // your page header
require("add.php"); // submits to index.php?todo=save
require("footer.php"); // your page footer
break;
case "edit":
// pull the data from db and plugs into form
$title = "Edit Existing Entry"; // used by header
require("header.php"); // your page header
require("edit.php"); // edits $editid, submits to index.php?todo=update
require("footer.php"); // your page footer
break;
case "delete":
require("delete.php"); // deletes $deleteid from the db
header("Location: index.php"); // go to listing
break;
case "save":
require("save.php"); // add new entry to db
header("Location: index.php"); // go to listing
break;
case "update":
require("update.php"); // update the db where id=$id
header("Location: index.php"); // go to listing
break;
default:
$title = "Viewing All Entries"; // used by header
require("header.php"); // your page header
require("list.php"); //show all entries
require("footer.php"); // your page footer
break;
}
?>
----------------------------------------------------------------------------------
list.php
----------------------------------------------------------------------------------
<link href="style.css" rel="stylesheet" media="screen">
<div align="center">
<span class="headers">Test Database Template</span>
<p>
<span class="bodystyle"><a href="index.php?todo=add">Add a Record</a></span><br>
<br>
</p>
<table width="650" cellspacing="2" border="0" cellpadding="1">
<tr bgcolor="#DADADA" align="center">
<td>ID</td>
<td>Load Date</td>
<td>Originating City</td>
<td>Originating State</td>
<td>Destination City</td>
<td>Destination State</td>
<td>Delete</td>
<td>Edit</td>
</tr>
<?php
mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name);
$result = mysql_query("select * from $db_table");
while($r=mysql_fetch_array($result))
{
$id=$r['id'];
$loaddate=$r['loaddate'];
$origcity=$r['origcity'];
$origstate=$r['origstate'];
$destcity=$r['destcity'];
$deststate=$r['deststate'];
print '
<tr>
<td>' . $id . '</td>
<td>' . $loaddate . '</td>
<td>' . $origcity . '</td>
<td>' . $origstate . '</td>
<td>' . $destcity . '</td>
<td>' . $deststate . '</td>
<td><a href="mailto:' . $email . '">' . $email . '</a></td>
<td><a href="index.php?todo=delete&deleteid=' . $id . '">delete</td>
<td><a href="index.php?todo=edit&editid=' . $id . '">edit</td>
</tr>';
}
?>
</table>
<br>
<span class="bodystyle"> <a href="#top">Top of Page</a> </span>
</div>
----------------------------------------------------------------------------------
save.php
----------------------------------------------------------------------------------
<?
mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name);
$result = mysql_query("INSERT INTO $db_table (id,loaddate,origcity,origstate,destcity,deststate) VALUES ('NULL', '$loaddate', '$origcity', '$origstate', '$destcity', '$deststate')");
?>
----------------------------------------------------------------------------------
update.php
----------------------------------------------------------------------------------
<?
mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name);
$result = mysql_query("UPDATE $db_table SET loaddate=$loaddate,origcity=$origcity,origstate=$origstate,destcity=$destcity,deststate=$deststate WHERE id=$id");
?>
----------------------------------------------------------------------------------
vars.php
----------------------------------------------------------------------------------
This page just holds the actual variables, which I can't post for obvious security reasons but we have already established that all of these are correct and working because we can connect and add records using the variable names.
I hope I haven't forgotten anything...oh yeah - HELP!
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
There's something wrong with your database connection, the database you are trying to select or your query so you need to add some error handling. Try changing:
to
Mac
Code: Select all
mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name);
$result = mysql_query("SELECT * FROM $db_table WHERE id=$editid");Code: Select all
mysql_connect($db_host, $db_user, $db_pass) or die(mysql_error());
mysql_select_db($db_name) or die(mysql_error());
$sql = "SELECT * FROM $db_table WHERE id=$editid";
$result = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');- DiamondLil
- Forum Newbie
- Posts: 15
- Joined: Fri Oct 04, 2002 11:14 am
twigletmac - thanks for responding. I'd be hard pressed to think it was the connection, because I'm connecting the same way to add records and that part works.
Also, it can't be the actual database because I can add records through these php pages as well as add/edit/delete through a telnet shell.
So then it must be the query as you suggest - but I can't see what I've got incorrect. On what page should I change that query ?
Ah - ok, probably on the edit.php...let me give it a shot...
Also, it can't be the actual database because I can add records through these php pages as well as add/edit/delete through a telnet shell.
So then it must be the query as you suggest - but I can't see what I've got incorrect. On what page should I change that query ?
Ah - ok, probably on the edit.php...let me give it a shot...
- DiamondLil
- Forum Newbie
- Posts: 15
- Joined: Fri Oct 04, 2002 11:14 am
ah ha.
I'm getting the following errors:
Warning: Lost connection to MySQL server during query in /usr/local/etc/httpd/htdocs/prologi/editbox/list.php on line 20
Warning: MySQL Connection Failed: Lost connection to MySQL server during query in /usr/local/etc/httpd/htdocs/prologi/editbox/list.php on line 20
I'm getting the following errors:
Warning: Lost connection to MySQL server during query in /usr/local/etc/httpd/htdocs/prologi/editbox/list.php on line 20
Warning: MySQL Connection Failed: Lost connection to MySQL server during query in /usr/local/etc/httpd/htdocs/prologi/editbox/list.php on line 20
- DiamondLil
- Forum Newbie
- Posts: 15
- Joined: Fri Oct 04, 2002 11:14 am
confused
now when I click "edit" off the home I'm getting a parse error on the last line of edit.php, which is an end form tag???
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
- DiamondLil
- Forum Newbie
- Posts: 15
- Joined: Fri Oct 04, 2002 11:14 am
Well, I've found a missing ' and a missing ) - and no, they were nowhere near the line on which I was getting the error, but it fixed that error just the same. Now the parse error is on line 6 of edit.php (the last line before ?> and I can't see what it is that's wrong:
<?
mysql_connect($db_host, $db_user, $db_pass) or die(mysql_error());
mysql_select_db($db_name) or die (mysql_error());
$sql = "SELECT * FROM $db_table WHERE id=$editid";
$result = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>' )
$edit = mysql_fetch_array($result);
?>
The error is :
Parse error: parse error in /usr/local/etc/httpd/htdocs/prologi/editbox/edit.php on line 6
<?
mysql_connect($db_host, $db_user, $db_pass) or die(mysql_error());
mysql_select_db($db_name) or die (mysql_error());
$sql = "SELECT * FROM $db_table WHERE id=$editid";
$result = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>' )
$edit = mysql_fetch_array($result);
?>
The error is :
Parse error: parse error in /usr/local/etc/httpd/htdocs/prologi/editbox/edit.php on line 6
What about the missing ; at the end of your die() statement
$result = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>' )
$edit = mysql_fetch_array($result);
$result = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>' );
more often than not it is the line before the one stated that causes the error.
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
- DiamondLil
- Forum Newbie
- Posts: 15
- Joined: Fri Oct 04, 2002 11:14 am
oh jeez...I feel silly for missing that one... but thank you both for the info. That gives me a better idea of where to look when I get these errors.
I'm getting something else currently. But now it's an SQL syntax error in the same area:
<?
mysql_connect($db_host, $db_user, $db_pass) or die(mysql_error());
mysql_select_db($db_name) or die (mysql_error());
$sql = "SELECT * from $db_table WHERE ID=$editid";
$result = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');
$edit = mysql_fetch_array($result);
?>
Am I missing something else to which I have just become oblivious?
I'm getting something else currently. But now it's an SQL syntax error in the same area:
<?
mysql_connect($db_host, $db_user, $db_pass) or die(mysql_error());
mysql_select_db($db_name) or die (mysql_error());
$sql = "SELECT * from $db_table WHERE ID=$editid";
$result = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');
$edit = mysql_fetch_array($result);
?>
Am I missing something else to which I have just become oblivious?
- mydimension
- Moderator
- Posts: 531
- Joined: Tue Apr 23, 2002 6:00 pm
- Location: Lowell, MA USA
- Contact:
okay, i think i found your problem and its in your list.php file.
rewrite your while statement to look like this:
rewrite your while statement to look like this:
Code: Select all
<?php
while($r=mysql_fetch_array($result))
{
?>
<tr>
<td><?php print $rї'id']; ?></td>
<td><?php print $rї'loaddate']; ?></td>
<td><?php print $rї'origcity']; ?></td>
<td><?php print $rї'origstate']; ?></td>
<td><?php print $rї'destcity']; ?></td>
<td><?php print $rї'deststate']; ?></td>
<td><a href="mailto:<?php print $rї'email']; ?>"><?php print $rї'email']; ?></a></td>
<td><a href="index.php?todo=delete&deleteid=<?php print $rї'id']; ?>">delete</td>
<td><a href="index.php?todo=edit&editid=<?php print $rї'id']; ?>">edit</td>
</tr>
<?php
}
?>- DiamondLil
- Forum Newbie
- Posts: 15
- Joined: Fri Oct 04, 2002 11:14 am
hmm...thanks, MUCH less code (I did take out the extra <?php print $r['email']; ?>). My table is still a little whacked out though:
http://www.mc2ads.com/prologi/editbox/index.php
still can't delete records and I'm still getting an SQL syntax error on edit.php in these lines - supposedly on my SELECT * line:
<?
mysql_connect($db_host, $db_user, $db_pass) or die(mysql_error());
mysql_select_db($db_name) or die(mysql_error());
$sql = "SELECT * from loads WHERE ID=$editid";
$result = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');
$edit = mysql_fetch_array($result);
?>
http://www.mc2ads.com/prologi/editbox/index.php
still can't delete records and I'm still getting an SQL syntax error on edit.php in these lines - supposedly on my SELECT * line:
<?
mysql_connect($db_host, $db_user, $db_pass) or die(mysql_error());
mysql_select_db($db_name) or die(mysql_error());
$sql = "SELECT * from loads WHERE ID=$editid";
$result = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');
$edit = mysql_fetch_array($result);
?>
- mydimension
- Moderator
- Posts: 531
- Joined: Tue Apr 23, 2002 6:00 pm
- Location: Lowell, MA USA
- Contact:
- DiamondLil
- Forum Newbie
- Posts: 15
- Joined: Fri Oct 04, 2002 11:14 am
That is exactly what I just realized !! I have changed a few of the references and I'm making some - slow - progress. It may exactly what's wrong with the delete.php.
I'll post & let you know.
I'll post & let you know.
Last edited by DiamondLil on Mon Oct 07, 2002 11:48 am, edited 1 time in total.
- DiamondLil
- Forum Newbie
- Posts: 15
- Joined: Fri Oct 04, 2002 11:14 am
Yup - that was it on the delete ('course it only took you about 10 minutes to see it and me it took, oh, probably about 5 days to realize that along with a few other things..)
Now at least we are pulling the values and plugging them into the fields for the update - it is not yet updating them though.
Now at least we are pulling the values and plugging them into the fields for the update - it is not yet updating them though.