arrrgghhh - php & mysql not playing nice together..!

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
DiamondLil
Forum Newbie
Posts: 15
Joined: Fri Oct 04, 2002 11:14 am

arrrgghhh - php & mysql not playing nice together..!

Post by DiamondLil »

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!
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

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:

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");
to

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().'&lt;p&gt;'.$sql.'&lt;/p&gt;');
Mac
User avatar
DiamondLil
Forum Newbie
Posts: 15
Joined: Fri Oct 04, 2002 11:14 am

Post by DiamondLil »

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...
User avatar
DiamondLil
Forum Newbie
Posts: 15
Joined: Fri Oct 04, 2002 11:14 am

Post by DiamondLil »

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
User avatar
DiamondLil
Forum Newbie
Posts: 15
Joined: Fri Oct 04, 2002 11:14 am

confused

Post by DiamondLil »

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???
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

What does the parse error say?

Mac
User avatar
DiamondLil
Forum Newbie
Posts: 15
Joined: Fri Oct 04, 2002 11:14 am

Post by DiamondLil »

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
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »


$result = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>' )
$edit = mysql_fetch_array($result);
What about the missing ; at the end of your die() statement


$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.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

To extend a bit what mikeq said - parse errors are reported on the line where PHP finds that it cannot continue parsing the script. Often this means that the parse error is reported a few lines below where the actual error has been made.

Mac
User avatar
DiamondLil
Forum Newbie
Posts: 15
Joined: Fri Oct 04, 2002 11:14 am

Post by DiamondLil »

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?
User avatar
mydimension
Moderator
Posts: 531
Joined: Tue Apr 23, 2002 6:00 pm
Location: Lowell, MA USA
Contact:

Post by mydimension »

okay, i think i found your problem and its in your list.php file.

rewrite your while statement to look like this:

Code: Select all

&lt;?php
while($r=mysql_fetch_array($result))
{
?&gt;
&lt;tr&gt;
        &lt;td&gt;&lt;?php print $r&#1111;'id']; ?&gt;&lt;/td&gt;
        &lt;td&gt;&lt;?php print $r&#1111;'loaddate']; ?&gt;&lt;/td&gt;
        &lt;td&gt;&lt;?php print $r&#1111;'origcity']; ?&gt;&lt;/td&gt;
        &lt;td&gt;&lt;?php print $r&#1111;'origstate']; ?&gt;&lt;/td&gt;
        &lt;td&gt;&lt;?php print $r&#1111;'destcity']; ?&gt;&lt;/td&gt;
        &lt;td&gt;&lt;?php print $r&#1111;'deststate']; ?&gt;&lt;/td&gt;
        &lt;td&gt;&lt;a href="mailto:&lt;?php print $r&#1111;'email']; ?&gt;"&gt;&lt;?php print $r&#1111;'email']; ?&gt;&lt;/a&gt;&lt;/td&gt;
        &lt;td&gt;&lt;a href="index.php?todo=delete&amp;deleteid=&lt;?php print $r&#1111;'id']; ?&gt;"&gt;delete&lt;/td&gt;
        &lt;td&gt;&lt;a href="index.php?todo=edit&amp;editid=&lt;?php print $r&#1111;'id']; ?&gt;"&gt;edit&lt;/td&gt;
&lt;/tr&gt;
&lt;?php
}
?&gt;
User avatar
DiamondLil
Forum Newbie
Posts: 15
Joined: Fri Oct 04, 2002 11:14 am

Post by DiamondLil »

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);
?>
User avatar
mydimension
Moderator
Posts: 531
Joined: Tue Apr 23, 2002 6:00 pm
Location: Lowell, MA USA
Contact:

Post by mydimension »

something i just realised. in your database "ID" is in caps but when you've been refering to the array you've been using "id" (no caps). in other words case matters and if its all caps in the database then it will be all caps in the array.
User avatar
DiamondLil
Forum Newbie
Posts: 15
Joined: Fri Oct 04, 2002 11:14 am

Post by DiamondLil »

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.
Last edited by DiamondLil on Mon Oct 07, 2002 11:48 am, edited 1 time in total.
User avatar
DiamondLil
Forum Newbie
Posts: 15
Joined: Fri Oct 04, 2002 11:14 am

Post by DiamondLil »

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.
Post Reply