Forum Moderators: coopster
So, say I had a database that looked similar to this one.
------------------
¦ Number ¦ Name ¦
-----------------
¦1_______¦Bob___¦
¦2_______¦Adam__¦
¦3_______¦Andrew¦
¦4_______¦Will__¦
¦5_______¦Don___¦
¦6_______¦Homer_¦
¦7_______¦Glen__¦
¦8_______¦Nate__¦
¦9_______¦Alex__¦
------------------
Keep in mind that the "Number" is an actual field.
How could I select one cell...that is by coding some functions that would say to select "Name" from row 4...is that possible? To find the Number and then tell the Name that is beside it?
Thanks to whoever can tell me!
[edited by: Flurpal at 4:37 pm (utc) on April 22, 2009]
// possible values: 0=anywhere,1=starts with,2=ends with,3=exact
$type = $_POST['search_type'];
$value = $_POST['q'];
$select = 'select Number,Name from your_table where';
// Note the space before "Name" below, important
if ($type == 0) { $select .= " Name like '%$value%'"; }
else if ($type == 1) { $select .= " Name like '%$value'"; }
else if ($type == 2) { $select .= " Name like '$value%'"; }
else if ($type == 3) { $select .= " Name = '$value'"; }
else { echo "Error, invalid search type supplied"; exit; }
echo "<ul>";
$result=@mysql_query("$select");
// Don't use this next line in LIVE apps,
// it reveals too much about your database
// if (!$result) { error("can't query table: " . mysql_error()); }
while ($row=mysql_fetch_array($result)) {
echo "<li> id: " . $row[0] . " Name: " . $row[1] . "</li>\n";
}
mysql_free_result($result);
echo "</ul>";
You're SELECTing the Name FROM the table WHERE the Number = # so your query would be:
SELECT Name FROM my_table WHERE Number = 4
If you're using PHP, you should plug this query into the PHP code that rocknbil provided.
What I want to happen, is:
The page for a specific game will call to the database for the information of that game...such as the description. Then it will display the description on that game page.
game_details.php?num=123
Note I didn't name it "Number" or any database field name. Cleanse it first:
if (isset($_GET['num'])) {
$num = $_GET['num'];
if (! preg_match('/^\d+$/',$num)) {
header("content-type:text/html\n\n");
echo "invalid request";
exit 0;
}
}
else {
header("content-type:text/html\n\n");
echo "invalid request";
exit 0;
}
What this does is insures the input begins with (^) and ends with ($) one or more digits (\d+), and nothing else, stopping any attempt at mysql injection via this entry point.
Then go on to use the select statement rob describes, using $num in place of #. You can also select all rows by *
select * from table where rec_id = $num
So $rows will contain however many columns there are; $row[1] is id, $row[2] is rec_id, $row[3] is Name, so on . . . .
I used "rec_id" because using the autoincrement field for a key or identifier is a bad habit to form, discussion for another thread.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Flurpal Database Test</title>
<meta http-equiv="content-type"
content="text/html; charset=iso-8859-1" />
</head>
<body>
<h1>Flurpal Database Test</h1>
<?php
$con = mysql_connect("localhost","user","pass");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("fullyub1_games", $con);
if (isset($_GET['num'])) {
$num = $_GET['num'];
if (! preg_match('/^\d+$/',$num)) {
header("content-type:text/html\n\n");
echo "invalid request";
exit ;
}
}
else {
header("content-type:text/html\n\n");
echo "invalid request";
exit ;
}
$query="SELECT Name FROM allgames WHERE Number = 1";
$result=mysql_query($query);
mysql_close($con);
?>
</body>
</html>
[edited by: eelixduppy at 2:13 am (utc) on April 24, 2009]
[edit reason] removed db specifics [/edit]
If there is nothing in the database for that query, you will want to be sending out a 404 HTTP Header - but it is too late to do that if you have already sent the DOCTYPE out.
You'll need to change the order of your code quite substantially. do it now while the code is still quite simple.
I am also sure you can use a HTML 4.01 DOCTYPE here. No need for XHTML.
I have not fixed it for you, this is how you learn. I've moved this around per g1smd's suggestions, but in answer to "it's not working" - note the bolded code at the beginning that turns error reporting on. This might help.
Note I've changed my first reply to concatenating a variable instead of doing an echo. This puts all results in $output.
<?php
// COMMENT OUT BEFORE GOING LIVE!
error_reporting(E_ALL);
ini_set('display_errors', '1');
$con = mysql_connect("localhost","user","pass");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("fullyub1_games", $con);
if (isset($_GET['num'])) {
$num = $_GET['num'];
if (! preg_match('/^\d+$/',$num)) {
header("content-type:text/html\n\n");
echo "invalid request";
exit ;
}
}
else {
header("content-type:text/html\n\n");
echo "invalid request";
exit ;
}
var $output=NULL;
$query="SELECT Name FROM allgames WHERE Number = 1";
// A note here, to select all records do
//$query="SELECT Name FROM allgames order by Name";
$result=@mysql_query($query);
// Don't use this next line in LIVE apps,
// it reveals too much about your database
// if (!$result) { die("can't query table: " . mysql_error()); }
while ($row=mysql_fetch_array($result)) {
// Since you're only selecting Name, there will be no "$row[1]"
$output .= "<li> Name: " . $row[0] . "</li>\n";
}
mysql_free_result($result);
mysql_close($con);
if is_null($output) { $output = '<li>NO RECORDS FOUND</li>'; }
header("content-type:text/html\n\n");
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" "http://www.w3.org/TR/1998/REC-html40-19980424/loose.dtd">
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Database Test</title>
</head>
<body>
<h1>Flurpal Database Test</h1>
<?php
<ul>
$output
</ul>
?>
</body>
</html>
<?php
<ul>
$output
</ul>
?>
Should be
<?php
echo "
<ul>
$output
</ul>
";
?>
Another,
$con = mysql_connect("localhost","user","pass");
You would put your mysql user and pass in place of the strings "user" and "pass".
The new code still has problems but should allow you to build the application so that it can be made to work just fine.
The original code was unfixable, in that after you got it to "work" you would have then uncovered a load more problems.
We took steps to fix those other problems first, because they are fundamental to the operation of a site that uses a database.
However, each row in the table is going to cover information on one game...with fields for Name, Description, Instructions, etc. So how could I set this up so that I could do this same thing over and over, in the same page, without repeating that whole block of code over and over? Which parts can I put to an external file, and what code do I have to add to do so? Thanks!
Here's the code I have so far...
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" "http://www.w3.org/TR/1998/REC-html40-19980424/loose.dtd">
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Database Test</title>
</head>
<body>
<h1>Flurpal Database Test</h1>
<?php
// COMMENT OUT BEFORE GOING LIVE!
error_reporting(E_ALL);
ini_set('display_errors', '1');
$con = mysql_connect("localhost","USER","PASS");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("DATABASE", $con);
$output=NULL;
$query="SELECT Name FROM allgames WHERE Number = 1";
//$query="SELECT Name FROM allgames order by Name";
$result=@mysql_query($query);
mysql_error()); }
while ($row=mysql_fetch_array($result)) {
$output .= "" . $row[0] . "\n";
}
mysql_free_result($result);
mysql_close($con);
?>
<?php
echo "
$output
";
?>
</body>
</html>
[edited by: Flurpal at 1:39 pm (utc) on April 26, 2009]
Study post 3899761 again, and look closely at where the DOCTYPE line is in that example, and then compare it to your code.
So how could I set this up so that I could do this same thing over and over, in the same page, without repeating that whole block of code over and over?
You do something like this:
if (isset($_GET['num'])) { // Do everything discussed to this point }
else { // output a "page index" }
The "page index" would go something like this:
$query = "select Number,Name,description from your_games_table order by posted_date";
(do your query)
while ($row=mysql_fetch_array($result)) {
$output .= '<li><a href=yourscript.php?num="' . $row[0] . '">' . $row[1] . '</a> " . $row[2] . </li>\n";
}
Or use "$row['Number'], $row['Name'], $row['description']" ....
So when those links are clicked, it sends 'num' in $_GET, which does the stuff we've been talking about, getting specific records.
