Help a newbie: blog software (updating PHP and MySQL issue)

[El Ornitorrico]

For my final project for my web development class I am coding a blog.  The script needs to be able to add entries (the easy part), then edit the entries, with the ability to delete them as well. 

I've been able to connect and add to the database in PHP, albeit after many hours of struggle, and now I'm going crazy trying to get the update script to work.  My book (Larry Ullman's PHP and MySQL, second edition) was no help, and when I inquired of my teacher, she told me to figure it out myself.  I found a most excellent guide on PHP and MySQL (which is how I learned to make and insert elements into databases), but it went all fuzzy and useless on updating databases. 

Lucky then, I thought, when I found out that you could download all the source files from the examples.  Naturally, everything worked except the update script. 

I've spent a good 15 hours on this now, including the time it took me to figure out how to add to the database in PHP and that's 25 hours.  This is due Friday and I'm getting desperate. 

The code from the example(again, not my code):
[code]
<?php
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die("Unable to select database");
$query="SELECT * FROM contacts WHERE id='$id'";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();

$i=0;
while ($i < $num) {
$first=mysql_result($result,$i,"first");
$last=mysql_result($result,$i,"last");
$phone=mysql_result($result,$i,"phone");
$mobile=mysql_result($result,$i,"mobile");
$fax=mysql_result($result,$i,"fax");
$email=mysql_result($result,$i,"email");
$web=mysql_result($result,$i,"web");

?>

<form action="updated.php">
<input type="hidden" name="ud_id" value="<?php echo "$id"; ?>">
First Name: <input type="text" name="ud_first" value="<?php echo "$first"?>"><br>
Last Name: <input type="text" name="ud_last" value="<?php echo "$last"?>"><br>
Phone Number: <input type="text" name="ud_phone" value="<?php echo "$phone"?>"><br>
Mobile Number: <input type="text" name="ud_mobile" value="<?php echo "$mobile"?>"><br>
Fax Number: <input type="text" name="ud_fax" value="<?php echo "$fax"?>"><br>
E-mail Address: <input type="text" name="ud_email" value="<?php echo "$email"?>"><br>
Web Address: <input type="text" name="ud_web" value="<?php echo "$web"?>"><br>
<input type="Submit" value="Update">
</form>

<?php
++$i;
}
?>
[/code]

This presents an amazing blank page.  Also, the dbinfo.inc.php is correct, as it works in all the other scripts. 

My attempt at making it work with the help of a different tutorial:

[code]
<?php
$page_title = "Messin' around with PHP and MySQL";
include("includes/header.inc.html");

include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die("Unable to select database");

$query  = "SELECT * FROM contacts";
$result = @mysql_query($query) or die("Error ".mysql_errno().": ".mysql_error()."\nQuery: $query");

while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
    echo "ID :{$row['id']} <br>" .
        "Name : {$row['first']}" .
        " {$row['last']} <br>" .
        "Phone : {$row['phone']} <br>" .
        "Mobile : {$row['mobile']} <br>" .
        "Fax  : {$row['fax']} <br>" .
        "email  : {$row['email']} <br>" .
        "web  : {$row['web']} <br><br>";

}

/*
$query="SELECT * FROM contacts WHERE id=*";
$result=mysql_query($query);
$num=mysql_numrows($result);
*/

$num = sizeof($row);


print "<p> $num this is a test</p>";
for ($i = 0; $i < $num; $i++) {
$first=mysql_result($result,$i,"first");
$last=mysql_result($result,$i,"last");
$phone=mysql_result($result,$i,"phone");
$mobile=mysql_result($result,$i,"mobile");
$fax=mysql_result($result,$i,"fax");
$email=mysql_result($result,$i,"email");
$web=mysql_result($result,$i,"web");

print "<form action='updated.php'>";
print "<input  name='ud_id' value=$id; ><br />";
print "First Name: <input type='text' name='ud_first' value=$first><br>";
print "Last Name: <input type='text' name='ud_last' value=$last><br>";
print "Phone Number: <input type='text' name='ud_phone' value=$phone><br>";
print "Mobile Number: <input type='text' name='ud_mobile' value=$mobile><br>";
print "Fax Number: <input type='text' name='ud_fax' value=$fax><br>";
print "E-mail Address: <input type='text' name='ud_email' value=$email><br>";
print "Web Address: <input type='text' name='ud_web' value=$web><br>";
print "<input type='Submit' value='Update'>";
print "</form>";

}
include("includes/footer.inc.html");
mysql_close();
?>
[/code]

which gives me [url=http://www.wvcweb.com/web2fall06/harline/Excersizes/example/update.php]this.[/url]

Oi ve. 

It should have one edit box for each entry in the database, and text fields should be filled in with the database information.

Is there anything which is wrong, or is the logic bad?  I can't imagine that the first one has bad logic, but the second most likely does.  I'd rather fix up the first one than the second, however. 

All help appreciated and all the best,
El Ornitorrinco

[hitman6003]

In your first example, where is the value of the variable $id coming from?  If there is no value assigned to it you will not see anything on your page because it only displays html if at least one record was returned from the database.

[code]<?php
include("dbinfo.inc.php");

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die("Unable to select database");

$query = "SELECT * FROM contacts WHERE id='$id'";
$result = mysql_query($query);
$num = mysql_numrows($result);
mysql_close();

$i=0;

if ($num > 0) {
while ($i < $num) {
$first=mysql_result($result,$i,"first");
$last=mysql_result($result,$i,"last");
$phone=mysql_result($result,$i,"phone");
$mobile=mysql_result($result,$i,"mobile");
$fax=mysql_result($result,$i,"fax");
$email=mysql_result($result,$i,"email");
$web=mysql_result($result,$i,"web");

?>

<form action="updated.php">
<input type="hidden" name="ud_id" value="<?php echo "$id"; ?>">
First Name: <input type="text" name="ud_first" value="<?php echo "$first"?>"><br>
Last Name: <input type="text" name="ud_last" value="<?php echo "$last"?>"><br>
Phone Number: <input type="text" name="ud_phone" value="<?php echo "$phone"?>"><br>
Mobile Number: <input type="text" name="ud_mobile" value="<?php echo "$mobile"?>"><br>
Fax Number: <input type="text" name="ud_fax" value="<?php echo "$fax"?>"><br>
E-mail Address: <input type="text" name="ud_email" value="<?php echo "$email"?>"><br>
Web Address: <input type="text" name="ud_web" value="<?php echo "$web"?>"><br>
<input type="Submit" value="Update">
</form>

<?php
++$i;
}
} else {
echo 'No results returned from the database.  Value of ID is: ' . $id;
}
?>[/code]

[El Ornitorrico]

Ah, thanks for that.

[quote=The Tutorial]
By using the $id variable you output links which would pass the correct ID to the script so that it can update the database. Using this you can then create the update script, which will actually have two sections to it.
[/quote]

Before this page of the tutorial:
[quote=The Tutorial]
Selecting A Single Record

At the end of the last part of this tutorial, I s
howed you how to select records from the database based on the contents of partiular fields using:

SELECT * FROM contacts WHERE field='value'

Now, by using the unique ID field we can select any record from our database using:

SELECT * FROM contacts WHERE id='$id'

Where $id is a variable holding a number of a record. This may seem to be a little worthless as it is, but you can use this very effectively in a number of different ways. For example, if you wanted to have a dynamically generated site run through a database and a single PHP script, you could write the script to include the database data into the design. Then, using the id field, you could select each individual page and put it into the output. You can even use the page's URL to specify the record you want e.g.

http://www.yoursite.com/news/items.php?item=7393

And then have the PHP script look up the record with the id corresponding to $item, which in this case would be 7393

Links For Single Records

Using this method of choosing a record using the URL to select the record can be expanded further by generating the URL dynamically. This sounds a bit complicated so I will elaborate. In the contacts script we are writing, I will be showing you how to create an Update page where the user can update the contact details.

To do this, another column will be included in the output column, with an Update link in it. This update link will point to a page allowing the user to update the record. To select the record in this page, we will put:

?id=$id

By getting the id of the record along with the other information when we are outputting the information from the database, this code will create a link which has each record's ID number in it. Then, on the update page, there can be code to just select this item.
[/quote]

Unfortunately, this doesn't make a lick of sense to me, as it's not written in newbie.  Could someone translate for me?  It looks to me like it was a piece of code which he intended to make work but then never did.  That bugger. 

I've printed a table from a multidimensional array before, so I figured this wouldn't be any harder...  Boy was I wrong. 

I dunno how I would modify that so that it would have an $id, because I don't understand what it's supposed to be doing.  =(

[hitman6003]

When you are looking to update a record in a database, such as a blog entry, the overall idea is:

- list the database records, usually in a table, each table row contains an entry along with a link to the page that will display the data for editing.  The link will usually be something like "edit.php?id=####", where #### is the unique id of that record.
- edit.php will retrieve the id ($_GET['id']) and use it in a query to retrieve the data from the database.  Then take that data, and place it into the form fields for editing.
- the user will edit the values, then submit the form to another page, "update.php".  That page will receive the input, then run an UPDATE query to change the values in the database.

[El Ornitorrico]

THANK YOU.  I've got to eat dinner now, but you're my hero.  I'll get to that immediately after.

[El Ornitorrico]

OK, so I think I got it. 

[quote author=hitman6003 link=topic=118379.msg483722#msg483722 date=1165974390]
- list the database records, usually in a table, each table row contains an entry along with a link to the page that will display the data for editing.  The link will usually be something like "edit.php?id=####", where #### is the unique id of that record.
[/quote]

[code]
<?php
$page_title = "index1.foop";
include("includes/header.inc.html");
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM contacts";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

echo "<b><center>Database Output</center></b><br><br>";

?>
<table border="0" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Name</font></th>
<th><font face="Arial, Helvetica, sans-serif">Phone</font></th>
<th><font face="Arial, Helvetica, sans-serif">Mobile</font></th>
<th><font face="Arial, Helvetica, sans-serif">Fax</font></th>
<th><font face="Arial, Helvetica, sans-serif">E-mail</font></th>
<th><font face="Arial, Helvetica, sans-serif">Website</font></th>
</tr>

<?
$i=0;
while ($i < $num) {
$first=mysql_result($result,$i,"first");
$last=mysql_result($result,$i,"last");
$phone=mysql_result($result,$i,"phone");
$mobile=mysql_result($result,$i,"mobile");
$fax=mysql_result($result,$i,"fax");
$email=mysql_result($result,$i,"email");
$web=mysql_result($result,$i,"web");
?>


<tr>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$first $last"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$phone"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$mobile"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$fax"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><a href="mailto:<? echo "$email"; ?>">E-mail</a></font></td>
<td><font face="Arial, Helvetica, sans-serif"><a href="<? echo '$web'; ?>">Website</a></font></td>
<td><font face="Arial, Helvetica, sans-serif"><a href="edit.php?id=<?php echo $i+1; ?>">Edit</a></font></td>
</tr>
<?
++$i;
}
echo "</table>";
include("includes/footer.inc.html");
?>
[/code]

Makes [url=http://www.wvcweb.com/web2fall06/harline/Excersizes/example/index1.php]this.[/url]

[quote author=hitman6003 link=topic=118379.msg483722#msg483722 date=1165974390]
- edit.php will retrieve the id ($_GET['id']) and use it in a query to retrieve the data from the database.  Then take that data, and place it into the form fields for editing.
[/quote]

[code]
<?php
$page_title = "edit.foop";
include("includes/header.inc.html");
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$id = $_GET['id']

$query="SELECT * FROM contacts WHERE id = $id";
$result=mysql_query($query);

$first=mysql_result($result,"first");
$last=mysql_result($result,"last");
$phone=mysql_result($result,"phone");
$mobile=mysql_result($result,,"mobile");
$fax=mysql_result($result,"fax");
$email=mysql_result($result,"email");
$web=mysql_result($result,"web");

print "<form action='update1.php'>";
print "<input  name='ud_id' value=$id; ><br />";
print "First Name: <input type='text' name='ud_first' value=$first><br>";
print "Last Name: <input type='text' name='ud_last' value=$last><br>";
print "Phone Number: <input type='text' name='ud_phone' value=$phone><br>";
print "Mobile Number: <input type='text' name='ud_mobile' value=$mobile><br>";
print "Fax Number: <input type='text' name='ud_fax' value=$fax><br>";
print "E-mail Address: <input type='text' name='ud_email' value=$email><br>";
print "Web Address: <input type='text' name='ud_web' value=$web><br>";
print "<input type='Submit' value='Update'>";
print "</form>";

mysql_close();
?>
[/code]

[url=http://www.wvcweb.com/web2fall06/harline/Excersizes/example/edit.php?id=1]Makes this.[/url]  The error is on the query.

[quote author=hitman6003 link=topic=118379.msg483722#msg483722 date=1165974390]
- the user will edit the values, then submit the form to another page, "update.php".  That page will receive the input, then run an UPDATE query to change the values in the database.
[/quote]

[code]
<?php
$page_title = "Update.foop";
include("includes/header.inc.html");

$query="UPDATE contacts SET first='$ud_first', last='$ud_last', phone='$ud_phone', mobile='$ud_mobile', fax='$ud_fax', email='$ud_email', web='$ud_web' WHERE id='$ud_id'";
@mysql_select_db($database) or die( "Unable to select database");
mysql_query($query);
echo "Record Updated";

include("includes/footer.inc.html");
mysql_close();
?>
[/code]

Dunno what it makes because the edit page dunnit work.  ='(  The query looks right dunnit?

I've decided that the problem is not with $query but with $id.  It doesn't make any sense.  I've been looking at guides and stuff, and the format is as follows:
("SELECT * FROM example  WHERE name='name'")

[vai_ron]

[quote]

maybe this will help [url=http://www.kitebird.com/articles/peardb.html]http://www.kitebird.com/articles/peardb.html[/url]

[/quote]

[El Ornitorrico]

[quote author=vai_ron link=topic=118379.msg483902#msg483902 date=1165992067]
maybe this will help [url=http://www.kitebird.com/articles/peardb.html]http://www.kitebird.com/articles/peardb.html[/url]
[/quote]

Ehhh....  Don't mean to be a dipstick, but if it took me 15 hours to learn how to connect and add entries to a database, something tells me that I won't be able to learn OOP  in PHP and PEAR scripting by Friday.  =( 

I'm still looking at query tutorials, and things are still coming up blank.  My query should work!

$query="SELECT * FROM contacts WHERE id = $id";

Forgot a semi colon.  Shoot me.

[El Ornitorrico]

OK, am stuck once more. 

Go to my [url=http://www.wvcweb.com/web2fall06/harline/Excersizes/example/index1.php]Working page[/url] and click one of the three edit buttons.  As you can see, it's much better than an error, however it loads either the MySQL ID number or the ID set to it in PHP, I don't know which, nor why it's happening.

Edit:
The trick is to fetch an array.  =D