Page 1 of 2

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

Posted: Fri Oct 04, 2002 11:14 am
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!

Posted: Fri Oct 04, 2002 11:38 am
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

Posted: Fri Oct 04, 2002 11:46 am
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...

Posted: Fri Oct 04, 2002 12:16 pm
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

confused

Posted: Fri Oct 04, 2002 1:46 pm
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???

Posted: Sat Oct 05, 2002 6:25 am
by twigletmac
What does the parse error say?

Mac

Posted: Mon Oct 07, 2002 7:39 am
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

Posted: Mon Oct 07, 2002 7:44 am
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.

Posted: Mon Oct 07, 2002 7:57 am
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

Posted: Mon Oct 07, 2002 9:40 am
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?

Posted: Mon Oct 07, 2002 10:18 am
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;

Posted: Mon Oct 07, 2002 11:10 am
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);
?>

Posted: Mon Oct 07, 2002 11:24 am
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.

Posted: Mon Oct 07, 2002 11:33 am
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.

Posted: Mon Oct 07, 2002 11:46 am
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.