PHP SQL - PHP/MySQL Question

Author

PHP/MySQL Question

mpar612

2006-08-08, 6:58 pm

Hello,

I'm sorry to keep posting and to put my long code at the bottom of my
posts, but I'm under a lot pressure to get this done quickly. I am
trying to add delete/update buttons to my page. I could not figure out
how to do it all in the existing page, so I added a link to my main
page that links to a delete page (code for both pages is below). When
a user clicks on the delete button and screen loads (the delete page
code) with the isbn number for that particular row added to the url
(e.g. delete_record.php?isbn='%20.%20987654321%20.%20'). On the
delete page, I cannot retrieve any of the information from the row that
the user selected delete from on the main page. I have made several
attempts and spent several hours on this and can't seem to get it. I
hope this makes sense.

If I can get this working, I should be able to run a delete query on
the delete page that deletes the record from the database.

Thanks in advance!

My main page:
<?php

// Load PEAR DB
require 'DB.php';

// Connect to the database
$db =
DB::connect('mysql://mikepar8_hockey:testing@216.246.48.250/mikepar8_mikeparkermusiccom');
if (DB::isError($db)) { die ("Can't connect: " . $db->getMessage()); }

// Set up automatic error handling
$db->setErrorHandling(PEAR_ERROR_DIE);

// Set up fetch mode: rows as objects
$db->setFetchMode(DB_FETCHMODE_ASSOC);

// Jump out of PHP mode to make displaying all the HTML tags easier
?>

<?php

print "<table align=\"center\" border=\"1\" cellpadding=\"0\"
cellspacing=\"0\" bordercolor=\"#000000\">";

// Send the query to the database program and get all the rows back
$rows = $db->getAll('SELECT isbn, artist_name, album_title,
date_format(release_date, '%M %d, %Y') as release_date,
date_format(add_date, '%M %d, %Y') as add_date, description, price
FROM lounge');
foreach ($rows as $row) {
print "<tr><td>ISBN:</td><td>$row[isbn]</td></tr>
<tr><td>Artist Name:</td><td>$row[artist_name]</td></tr>
<tr><td>Album Title:</td><td>$row[album_title]</td></tr>
<tr><td>Release Date:</td><td>$row[release_date]</td></tr>
<tr><td>Add Date:</td><td>$row[add_date]</td></tr>
<tr><td>Description:</td><td>$row[description]</td></tr>
<tr><td>Price:</td><td>$row[price]</td></tr></tr>
<tr><td><a href=\"delete_record.php?isbn=' . $row[isbn] .
'\">Delete</a></td><td>Update</td></tr>
<tr><td colspan=\"2\"><hr></td></tr>";
}

?>

My delete page:
<?php

// Load PEAR DB
require 'DB.php';

// Load the form helper functions.
require 'formhelpers.php';

// Connect to the database
$db =
DB::connect('mysql://mikepar8_hockey:testing@216.246.48.250/mikepar8_mikeparkermusiccom');
if (DB::isError($db)) { die ("Can't connect: " . $db->getMessage()); }

// Set up automatic error handling
$db->setErrorHandling(PEAR_ERROR_DIE);

// Set up fetch mode: rows as objects
$db->setFetchMode(DB_FETCHMODE_ASSOC);

// Jump out of PHP mode to make displaying all the HTML tags easier
?>

<?php
// Send the query to the database program and get all the rows back

if ( (isset($_GET['isbn'])) && (is_numeric($_GET['isbn'])) ) {
$isbn = $_GET['isbn'];
} elseif ( (isset($_POST['isbn'])) && (is_numeric($_POST['isbn'])) )
{
$isbn = $_POST['isbn'];
} else {
echo '<h1>This page has been reached in error</h1>';
exit();
}

print "<table align=\"center\" border=\"1\" cellpadding=\"0\"
cellspacing=\"0\" bordercolor=\"#000000\">";

$rows = $db->getAll('SELECT isbn, artist_name, album_title,
date_format(release_date, '%M %d, %Y') as release_date,
date_format(add_date, '%M %d, %Y') as add_date, description, price
FROM lounge WHERE isbn=$isbn');
foreach ($rows as $row) {
print "<tr><td>ISBN:</td><td>$row[isbn]</td></tr>
<tr><td>Artist Name:</td><td>$row[artist_name]</td></tr>
<tr><td>Album Title:</td><td>$row[album_title]</td></tr>
<tr><td>Release Date:</td><td>$row[release_date]</td></tr>
<tr><td>Add Date:</td><td>$row[add_date]</td></tr>
<tr><td>Description:</td><td>$row[description]</td></tr>
<tr><td>Price:</td><td>$row[price]</td></tr></tr>";
}

?>

Juliette

2006-08-09, 3:58 am

mpar612 wrote:
> Hello,
>
> I'm sorry to keep posting and to put my long code at the bottom of my
> posts, but I'm under a lot pressure to get this done quickly. I am
> trying to add delete/update buttons to my page. I could not figure out
> how to do it all in the existing page, so I added a link to my main
> page that links to a delete page (code for both pages is below). When
> a user clicks on the delete button and screen loads (the delete page
> code) with the isbn number for that particular row added to the url
> (e.g. delete_record.php?isbn='%20.%20987654321%20.%20').

The quotes in the query string look weird / wrong to me.

On the
> delete page, I cannot retrieve any of the information from the row that
> the user selected delete from on the main page. I have made several
> attempts and spent several hours on this and can't seem to get it. I
> hope this makes sense.
>
> If I can get this working, I should be able to run a delete query on
> the delete page that deletes the record from the database.
>
> Thanks in advance!
>
> My main page:
> <?php
>
> // Load PEAR DB
> require 'DB.php';
>
> // Connect to the database
> $db =
> DB::connect('mysql://mikepar8_hockey:testing@216.246.48.250/mikepar8_mikeparkermusiccom');
> if (DB::isError($db)) { die ("Can't connect: " . $db->getMessage()); }
>
> // Set up automatic error handling
> $db->setErrorHandling(PEAR_ERROR_DIE);
>
> // Set up fetch mode: rows as objects
> $db->setFetchMode(DB_FETCHMODE_ASSOC);
>
> // Jump out of PHP mode to make displaying all the HTML tags easier
> ?>
>
> <?php
>
> print "<table align=\"center\" border=\"1\" cellpadding=\"0\"
> cellspacing=\"0\" bordercolor=\"#000000\">";
>
> // Send the query to the database program and get all the rows back
> $rows = $db->getAll('SELECT isbn, artist_name, album_title,
> date_format(release_date, '%M %d, %Y') as release_date,
> date_format(add_date, '%M %d, %Y') as add_date, description, price
> FROM lounge');
> foreach ($rows as $row) {
> print "<tr><td>ISBN:</td><td>$row[isbn]</td></tr>
> <tr><td>Artist Name:</td><td>$row[artist_name]</td></tr>
> <tr><td>Album Title:</td><td>$row[album_title]</td></tr>
> <tr><td>Release Date:</td><td>$row[release_date]</td></tr>
> <tr><td>Add Date:</td><td>$row[add_date]</td></tr>
> <tr><td>Description:</td><td>$row[description]</td></tr>
> <tr><td>Price:</td><td>$row[price]</td></tr></tr>
> <tr><td><a href=\"delete_record.php?isbn=' . $row[isbn] .
> '\">Delete</a></td><td>Update</td></tr>

Ok, so remove the single quotes around $row[isbn] here and urlencode
$row[isbn]
By the by, I shouldn't think this will work in the first place as the
array keys should be quoted, i.e. $row['isbn'] instead of $row[isbn].
You should also use htmlspecialchars for all html output too, so:
htmlspecialchars($row['artist_name'], ENT_QUOTES, your character encoding)
and
htmlspecialchars(urlencode($row['isbn'])
, ENT_QUOTES, your character
encoding)

> <tr><td colspan=\"2\"><hr></td></tr>";
> }
>
> ?>
>
>
> My delete page:
> <?php
>
> // Load PEAR DB
> require 'DB.php';
>
> // Load the form helper functions.
> require 'formhelpers.php';
>
> // Connect to the database
> $db =
> DB::connect('mysql://mikepar8_hockey:testing@216.246.48.250/mikepar8_mikeparkermusiccom');
> if (DB::isError($db)) { die ("Can't connect: " . $db->getMessage()); }
>
> // Set up automatic error handling
> $db->setErrorHandling(PEAR_ERROR_DIE);
>
> // Set up fetch mode: rows as objects
> $db->setFetchMode(DB_FETCHMODE_ASSOC);
>
> // Jump out of PHP mode to make displaying all the HTML tags easier
> ?>
>
> <?php
> // Send the query to the database program and get all the rows back
>
> if ( (isset($_GET['isbn'])) && (is_numeric($_GET['isbn'])) ) {
> $isbn = $_GET['isbn'];
> } elseif ( (isset($_POST['isbn'])) && (is_numeric($_POST['isbn'])) )
> {
> $isbn = $_POST['isbn'];
> } else {
> echo '<h1>This page has been reached in error</h1>';
> exit();
> }

Yup, you should always reach error as your _GET/ _POST value contains
the single quotes and therefore will not be numeric.

>
> print "<table align=\"center\" border=\"1\" cellpadding=\"0\"
> cellspacing=\"0\" bordercolor=\"#000000\">";
>

You are not escaping your _GET/_POST value before passing it to the
database. Bad practice.
Use mysql_real_escape_string or your database equivalent to escape the
string:
$isbn = mysql_real_escape_string($isbn);

> $rows = $db->getAll('SELECT isbn, artist_name, album_title,
> date_format(release_date, '%M %d, %Y') as release_date,
> date_format(add_date, '%M %d, %Y') as add_date, description, price
> FROM lounge WHERE isbn=$isbn');

After making the other suggested changes, change the last bit of the
query to:
WHERE isbn= "' . $isbn . '");

> foreach ($rows as $row) {
> print "<tr><td>ISBN:</td><td>$row[isbn]</td></tr>
> <tr><td>Artist Name:</td><td>$row[artist_name]</td></tr>
> <tr><td>Album Title:</td><td>$row[album_title]</td></tr>
> <tr><td>Release Date:</td><td>$row[release_date]</td></tr>
> <tr><td>Add Date:</td><td>$row[add_date]</td></tr>
> <tr><td>Description:</td><td>$row[description]</td></tr>
> <tr><td>Price:</td><td>$row[price]</td></tr></tr>";
> }
>
> ?>
>

So where do you do your delete ?

Comments quickly posted off-heart. Check the php manual for correct syntax.

Hope this helps,
Jrf

Peter Boosten

2006-08-09, 3:58 am

In alt.php Jonathan <jonathan@heelal.nl> wrote:
> mpar612 wrote:
>
> One advice: Usenet is archived and your password will be visible for
> years on end, never post you actual connection string including
> passwords! It is even better not to hardcode them, especially not in the
> script file itself.
>

Good that you repeated it, in case anyone missed it ;-)

Peter

--
http://www.boosten.org

Mail: peter at boosten dot org

Dennis de Wit

2006-08-09, 3:58 am

Peter Boosten wrote:
> In alt.php Jonathan <jonathan@heelal.nl> wrote:
>
> Good that you repeated it, in case anyone missed it ;-)
>
> Peter
>

:) My password is SecreT :)

Jonathan

2006-08-09, 7:58 am

mpar612 wrote:
> // Connect to the database
> $db =
>
DB::connect('mysql://xxxxxxx:xxxxxxx@216.246.48.250/mikepar8_mikeparkermusiccom');

One advice: Usenet is archived and your password will be visible for
years on end, never post you actual connection string including
passwords! It is even better not to hardcode them, especially not in the
script file itself.

I'm not claiming that it is the only and the best solution but I usually
use an include file for the parameters and place this include file in a
directory that is only readable to the web server daemon. You can reuse
this include file every time you need it so you only have to configure
the connection once and also have to change parameters in one file only.

In your include file you could define your dsn:

$dsn = 'mysql://someuser:apasswd@hostname/thedb'

or more readable:

$dsn = array(
'phptype' => 'mysql',
'username' => 'someuser',
'password' => 'apasswd',
'hostspec' => 'hostname',
'database' => 'thedb',
);

Connecting would than easily be:

$db =& DB::connect($dsn);

Last advice... the PEAR/DB package is no longer maintained and is
superseded by PEAR/MDB2. It depends on your way of coding, but it is
quite easy to modify your code to use MDB2 instead of DB. Documentation
for MDB2: http://pear.php.net/manual/en/package.database.mdb2.php
(Although it is still not complete: http://pooteeweet.org/blog/336)

For more info on migrating: http://www.phpied.com/db-2-mdb2/

Good luck,

Jonathan

Peter Boosten

2006-08-09, 7:58 am

In alt.php Jonathan <jonathan@heelal.nl> wrote:
>
> Oops, cancelled the message and replaced my comment with a obfuscated
> one... (althought the OP's original message will also still be available.
>

I forgive you :-)
Actually it was OP who made the mistake in first place.

Greetings,

Peter

--
http://www.boosten.org

Mail: peter at boosten dot org

mpar612

2006-08-09, 7:58 am

Jonathan wrote:
> mpar612 wrote:
> DB::connect('mysql://xxxxxxx:xxxxxxx@216.246.48.250/mikepar8_mikeparkermusiccom');
>
> One advice: Usenet is archived and your password will be visible for
> years on end, never post you actual connection string including
> passwords! It is even better not to hardcode them, especially not in the
> script file itself.
>
> I'm not claiming that it is the only and the best solution but I usually
> use an include file for the parameters and place this include file in a
> directory that is only readable to the web server daemon. You can reuse
> this include file every time you need it so you only have to configure
> the connection once and also have to change parameters in one file only.
>
> In your include file you could define your dsn:
>
> $dsn = 'mysql://someuser:apasswd@hostname/thedb'
>
> or more readable:
>
> $dsn = array(
> 'phptype' => 'mysql',
> 'username' => 'someuser',
> 'password' => 'apasswd',
> 'hostspec' => 'hostname',
> 'database' => 'thedb',
> );
>
> Connecting would than easily be:
>
> $db =& DB::connect($dsn);
>
> Last advice... the PEAR/DB package is no longer maintained and is
> superseded by PEAR/MDB2. It depends on your way of coding, but it is
> quite easy to modify your code to use MDB2 instead of DB. Documentation
> for MDB2: http://pear.php.net/manual/en/package.database.mdb2.php
> (Although it is still not complete: http://pooteeweet.org/blog/336)
>
> For more info on migrating: http://www.phpied.com/db-2-mdb2/
>
> Good luck,
>
> Jonathan

Thanks for the tips. I totally screwed up the login info. I meant to
sanitize, but realized that I didn't immediately after I clicked on
"Post Message". I tried to delete it, but that didn't work.

Thanks again, I appreciate it!

Jonathan

2006-08-09, 6:59 pm

mpar612 wrote:

> Thanks for the tips. I totally screwed up the login info. I meant to
> sanitize, but realized that I didn't immediately after I clicked on
> "Post Message". I tried to delete it, but that didn't work.
>
> Thanks again, I appreciate it!

You can try to cancel it, most proper newsreaders support this option.
If I select my message in my reader (Thunderbird) and I right click I am
able to select 'Cancel Message' This will normally delete the message
from the server (You can only cancle your own messages off course).

Jonathan

Sponsored Links