Home > Archive > PHP Language > February 2007 > php/mysql syntax help
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
php/mysql syntax help
|
|
| edward_sanders 2007-02-14, 6:59 pm |
| Hi,
This is a newbie question. I am using a text for learning php/mysql.
The example is that of a mysql
database of jokes. Before we get to joins there is a
simple table with 3 fields, ID field (primary key, integer),
JokeText, and JokeDate. In the program for some
reason the code is not retrieving the ID for each
row from the db. It gets the JokeText field just fine.
Let me include the snippets below.
This is where each row (each joke) is to be displayed.
Note that the ID is used to give the option of deleting a
joke from the db. The link tag is supposed to get that
ID passed to the link but that isn't happening. Please help.
// Display the text of each joke in the paragraph
while ( $row = mysql_fetch_array($result)) {
$jokeid = $row["ID"];
echo ($row["ID"]);
$deletejoke=$jokeid;
$joketext = $row["JokeText"];
$thispage = $_SERVER["PHP_SELF"];
echo("<p>$joketext " .
"<A HREF='$thispage?deletejoke=$jokeid'>" .
"Delete this Joke</a></p>");
}
Then the code to delete the joke ( the row from the
db) is as follows:
// If a joke has been deleted,
// remove it from the database
if (isset($_GET['deletejoke'])) {
$deletejoke=$_GET['deletejoke'];
echo("<p>The joke to delete is number $deletejoke");
$sql = "DELETE FROM jokes " .
"WHERE ID=$deletejoke";
if (mysql_query($sql)) {
echo("<p>The joke has been deleted.</p>");
} else {
echo("<p>Error deleting joke: " .
mysql_error() . "</p>");
}
}
Thanks in advance for any help,
Bruce
| |
| edward_sanders 2007-02-14, 6:59 pm |
| I meant to add that I am using PHP 5.2.x and
mysql 5
Thanks,
Bruce
"edward_sanders" <edward_sanders@bellsouth.net> wrote in message news:...
> Hi,
> This is a newbie question. I am using a text for learning php/mysql.
> The example is that of a mysql
> database of jokes. Before we get to joins there is a
> simple table with 3 fields, ID field (primary key, integer),
> JokeText, and JokeDate. In the program for some
> reason the code is not retrieving the ID for each
> row from the db. It gets the JokeText field just fine.
> Let me include the snippets below.
>
> This is where each row (each joke) is to be displayed.
> Note that the ID is used to give the option of deleting a
> joke from the db. The link tag is supposed to get that
> ID passed to the link but that isn't happening. Please help.
>
> // Display the text of each joke in the paragraph
> while ( $row = mysql_fetch_array($result)) {
> $jokeid = $row["ID"];
> echo ($row["ID"]);
> $deletejoke=$jokeid;
> $joketext = $row["JokeText"];
> $thispage = $_SERVER["PHP_SELF"];
> echo("<p>$joketext " .
> "<A HREF='$thispage?deletejoke=$jokeid'>" .
> "Delete this Joke</a></p>");
> }
>
> Then the code to delete the joke ( the row from the
> db) is as follows:
> // If a joke has been deleted,
> // remove it from the database
> if (isset($_GET['deletejoke'])) {
> $deletejoke=$_GET['deletejoke'];
> echo("<p>The joke to delete is number $deletejoke");
> $sql = "DELETE FROM jokes " .
> "WHERE ID=$deletejoke";
> if (mysql_query($sql)) {
> echo("<p>The joke has been deleted.</p>");
> } else {
> echo("<p>Error deleting joke: " .
> mysql_error() . "</p>");
> }
> }
>
> Thanks in advance for any help,
> Bruce
>
| |
| shimmyshack 2007-02-14, 6:59 pm |
| On 14 Feb, 23:11, "edward_sanders" <edward_sand...@bellsouth.net>
wrote:[color=darkred]
> I meant to add that I am using PHP 5.2.x and
> mysql 5
> Thanks,
> Bruce
>
> "edward_sanders" <edward_sand...@bellsouth.net> wrote in message news:...
>
>
>
>
you havent quite included the SELECT statement you are using, so we
cant help, but basically to get all the columns in the table the
syntax is
SELECT * FROM table .......
that will get you all 3.
As for the rest of your code. you NEED to be looking at the php
manual
mysql_real_escape_string()
and the MySQL manual for
LIMIT
else someone could write the following URL
http://server.com/script.php?delete...rop%20tablename
bye bye all jokes. *unless the user this app is running under is not
allowed to do this, however theres nothing to stop it deleting them
all, and leaving a blank table.
if ( isset($_POST['deletejoke']) &&
ereg( "[0-9]{1-3}",$_POST['deletejoke']) )
{
//this means that the var is set, and is a number between 0-999
//$deletejoke = (int)$_POST['deletejoke'];
}
else
{
//tell user "choose a single joke to delete using the interface
provided";
}
either before the delete query or during it, use the escape function
"DELETE from tablename WHERE `id` = " .
mysql_real_escape_string($deletejoke) .
" LIMIT 1;"
makes more sense.
You should be using POST since the user is changing the application,
the last thing you want is for someone's browser to prefetch all those
delete links.
| |
| shimmyshack 2007-02-14, 6:59 pm |
| On 14 Feb, 23:11, "edward_sanders" <edward_sand...@bellsouth.net>
wrote:[color=darkred]
> I meant to add that I am using PHP 5.2.x and
> mysql 5
> Thanks,
> Bruce
>
> "edward_sanders" <edward_sand...@bellsouth.net> wrote in message news:...
>
>
>
>
ps.
the same goes for any GET string you are including in your webpage,
without attention to cleaning them, your users can place any code they
wish into your page, which renders any login you might have,
ineffective, for instance someone could make a form which auto submits
to your login page, which injects javascript inside which reads the
password and sends it off prior to loggin in your user. easy to do,
just because GET or POST vars are being included into the webpage
without proper cleaning, using htmlentities and validation.
Im thinking here of this line:
"<A HREF='$thispage?deletejoke=$jokeid'>"
which can be used to deface your website etc...
| |
| Geoff Berrow 2007-02-14, 6:59 pm |
| Message-ID: <1171496329.713416.196210@a34g2000cwb.googlegroups.com> from
shimmyshack contained the following:
>You should be using POST since the user is changing the application,
>the last thing you want is for someone's browser to prefetch all those
>delete links.
Indeed. A search engine spider would have the same effect. Whoops, bye
bye jokes.
--
Geoff Berrow 0110001001101100010000000110
0011011010110110010001101111011001110010
11
1001100011011011110010111001110101011010
11
| |
| edward_sanders 2007-02-15, 6:59 pm |
| Ok, I was asked what the SELECT statement in my mysql was to get
an idea about the problem. I see that I might have some security risks if
I actually used this design on the web. So, for now it is for instructional
purposes and the actual code that I might leave on the web would
include several refinements.
The problem is with this syntax here:
// Display the text of each joke in the paragraph
while ( $row = mysql_fetch_array($result)) {
$jokeid = $row["ID"];
echo ($row["ID"]);
$deletejoke=$jokeid;
$joketext = $row["JokeText"];
$thispage = $_SERVER["PHP_SELF"];
echo("<p>$joketext " .
"<A HREF='$thispage?deletejoke=$jokeid'>" .
"Delete this Joke</a></p>");
}[color=darkred]
What I'm doing is taking a row from the database, which includes,
ID - primary key, JokeText and JokeData (not used here).
mysql_fetch_array($result)
which grabs one row at a time of the db. This works,
$joketext = $row["JokeText"];
but when I try to grab the ID it doesn't like that syntax. At this line,
"<A HREF='$thispage?deletejoke=$jokeid'>" .
The value of deletejoke is never getting assigned to by
$jokeid. Question
)> Can anyone help me figure out why that would not work...
why the ID field isn't being passed to the variable $jokeid ???
While it might be good to improve this code before publishing, I
cannot figure out why that line is not working. I'll include the entire
php code below, now.[color=darkred]
<?php
// Display form if the user selects the option to add a joke
if (isset($_GET['addjoke'])) :
?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" METHOD="POST">
<p>Type your joke here:</p><br />
<TEXTAREA NAME="joke" ROWS=10 COLS=40 WRAP>
</TEXTAREA><BR />
<INPUT TYPE=SUBMIT NAME="submitjoke" VALUE="SUBMIT">
</FORM>
<?php
else:
//echo ("This is a test <br />"); this was used for debugging
// Connect to the database server
$dbcnx = mysql_connect("localhost", "user", "password");
if ( !$dbcnx) {
echo("<p>Unable to connect to the " .
"database server at this time.</p>");
exit();
} /* else { some lines used for debugging here
echo("I connected");
} */
// Select the jokes database
if (!mysql_select_db("jokes", $dbcnx)) {
echo("<p>Unable to connect to the jokes" .
"database at this time.</p>");
exit();
}
// If a joke has been submitted,
// add it to the database
$joketext = $_POST['joke'];
if ("SUBMIT" == $_POST['submitjoke']) {
$sql = "INSERT INTO jokes SET " .
"JokeText='$joketext', " .
"JokeDate=CURDATE()";
if (mysql_query($sql)) {
echo("<p>Your joke has been added.</p>");
} else {
echo ("<p>Error adding submitted joke: " .
mysql_error() . "</p>");
}
}
// If a joke has been deleted,
// remove it from the database
if (isset($_GET['deletejoke'])) {
$deletejoke=$_GET['deletejoke'];
echo("<p>The joke to delete is number $deletejoke");
$sql = "DELETE FROM jokes " .
"WHERE ID=$deletejoke";
if (mysql_query($sql)) {
echo("<p>The joke has been deleted.</p>");
} else {
echo("<p>Error deleting joke: " .
mysql_error() . "</p>");
}
}
echo("<p>Here are all the jokes " .
"in our database: </p>");
// Request the text of all the jokes
$result = mysql_query("SELECT JokeText FROM jokes");
if ( !$result ) {
echo ("<p>Error performing query: " .
mysql_error() . "</p>");
exit();
}
echo("<blockquote>");
// Display the text of each joke in the paragraph
while ( $row = mysql_fetch_array($result)) {
$jokeid = $row["ID"];
echo ($row["ID"]);
$deletejoke=$jokeid;
$joketext = $row["JokeText"];
$thispage = $_SERVER["PHP_SELF"];
echo("<p>$joketext " .
"<A HREF='$thispage?deletejoke=$jokeid'>" .
"Delete this Joke</a></p>");
}
echo("</blockquote>");
// When clicked, this link will load this page
// with the joke submission form displayed.
?>
<p><a href="<?php echo $_SERVER['PHP_SELF']; ?>?addjoke=1">
Add a Joke, by clicking here!</a></p>
<?php
endif;
?>[color=darkred]
"shimmyshack" <matt.farey@gmail.com> wrote in message
news:1171496329.713416.196210@a34g2000cwb.googlegroups.com...[color=darkred]
> On 14 Feb, 23:11, "edward_sanders" <edward_sand...@bellsouth.net>
> wrote:
[color=darkred]
[color=darkred]
>
<snip>
>
>
> You should be using POST since the user is changing the application,
> the last thing you want is for someone's browser to prefetch all those
> delete links.
>
| |
| shimmyshack 2007-02-15, 6:59 pm |
| On 15 Feb, 22:25, "edward_sanders" <edward_sand...@bellsouth.net>
wrote:[color=darkred]
> Ok, I was asked what the SELECT statement in my mysql was to get
> an idea about the problem. I see that I might have some security risks if
> I actually used this design on the web. So, for now it is for instructional
> purposes and the actual code that I might leave on the web would
> include several refinements.
> The problem is with this syntax here:
>
> // Display the text of each joke in the paragraph
> while ( $row = mysql_fetch_array($result)) {
> $jokeid = $row["ID"];
> echo ($row["ID"]);
> $deletejoke=$jokeid;
> $joketext = $row["JokeText"];
> $thispage = $_SERVER["PHP_SELF"];
> echo("<p>$joketext " .
> "<A HREF='$thispage?deletejoke=$jokeid'>" .
> "Delete this Joke</a></p>");
> }
>
> What I'm doing is taking a row from the database, which includes,
> ID - primary key, JokeText and JokeData (not used here).
> mysql_fetch_array($result)
> which grabs one row at a time of the db. This works,
> $joketext = $row["JokeText"];
> but when I try to grab the ID it doesn't like that syntax. At this line,
> "<A HREF='$thispage?deletejoke=$jokeid'>" .
> The value of deletejoke is never getting assigned to by
> $jokeid. Question
> )> Can anyone help me figure out why that would not work...
> why the ID field isn't being passed to the variable $jokeid ???
>
> While it might be good to improve this code before publishing, I
> cannot figure out why that line is not working. I'll include the entire
> php code below, now.
>
> <?php
> // Display form if the user selects the option to add a joke
> if (isset($_GET['addjoke'])) :
> ?>
> <form action="<?php echo $_SERVER['PHP_SELF']; ?>" METHOD="POST">
> <p>Type your joke here:</p><br />
> <TEXTAREA NAME="joke" ROWS=10 COLS=40 WRAP>
> </TEXTAREA><BR />
> <INPUT TYPE=SUBMIT NAME="submitjoke" VALUE="SUBMIT">
> </FORM>
>
> <?php
> else:
>
> //echo ("This is a test <br />"); this was used for debugging
>
> // Connect to the database server
> $dbcnx = mysql_connect("localhost", "user", "password");
> if ( !$dbcnx) {
> echo("<p>Unable to connect to the " .
> "database server at this time.</p>");
> exit();
> } /* else { some lines used for debugging here
> echo("I connected");
> } */
>
> // Select the jokes database
> if (!mysql_select_db("jokes", $dbcnx)) {
> echo("<p>Unable to connect to the jokes" .
> "database at this time.</p>");
> exit();
> }
>
> // If a joke has been submitted,
> // add it to the database
> $joketext = $_POST['joke'];
> if ("SUBMIT" == $_POST['submitjoke']) {
> $sql = "INSERT INTO jokes SET " .
> "JokeText='$joketext', " .
> "JokeDate=CURDATE()";
> if (mysql_query($sql)) {
> echo("<p>Your joke has been added.</p>");
> } else {
> echo ("<p>Error adding submitted joke: " .
> mysql_error() . "</p>");
> }
> }
> // If a joke has been deleted,
> // remove it from the database
> if (isset($_GET['deletejoke'])) {
> $deletejoke=$_GET['deletejoke'];
> echo("<p>The joke to delete is number $deletejoke");
> $sql = "DELETE FROM jokes " .
> "WHERE ID=$deletejoke";
> if (mysql_query($sql)) {
> echo("<p>The joke has been deleted.</p>");
> } else {
> echo("<p>Error deleting joke: " .
> mysql_error() . "</p>");
> }
> }
>
> echo("<p>Here are all the jokes " .
> "in our database: </p>");
>
> // Request the text of all the jokes
> $result = mysql_query("SELECT JokeText FROM jokes");
>
> if ( !$result ) {
> echo ("<p>Error performing query: " .
> mysql_error() . "</p>");
> exit();
> }
>
> echo("<blockquote>");
> // Display the text of each joke in the paragraph
> while ( $row = mysql_fetch_array($result)) {
> $jokeid = $row["ID"];
> echo ($row["ID"]);
> $deletejoke=$jokeid;
> $joketext = $row["JokeText"];
> $thispage = $_SERVER["PHP_SELF"];
> echo("<p>$joketext " .
> "<A HREF='$thispage?deletejoke=$jokeid'>" .
> "Delete this Joke</a></p>");
> }
> echo("</blockquote>");
>
> // When clicked, this link will load this page
> // with the joke submission form displayed.
> ?>
> <p><a href="<?php echo $_SERVER['PHP_SELF']; ?>?addjoke=1">
> Add a Joke, by clicking here!</a></p>
>
> <?php
> endif;
> ?>
>
>
>
> "shimmyshack" <matt.fa...@gmail.com> wrote in message
>
> news:1171496329.713416.196210@a34g2000cwb.googlegroups.com...
>
>
>
>
>
>
>
> <snip>
>
>
>
Hiya, here is the select statement:
$result = mysql_query("SELECT JokeText FROM jokes");
notice that it doesnt have a star in it. so as per my previous
posting, use this instead:
$result = mysql_query("SELECT * FROM jokes");
now you WILL be returning a whole row, not just one of the columns.
|
|
|
|
|