PHP/ MySQL Help

herrpoon · 24 May 2007 at 17:15

Hi there, I am in need of some guidance! I've tried looking around online for some tutorials although nothing has come up...

At the moment, I have created a MySQL database, with a table and 10 fields in it, what I want to be able to do is to display the data just from one page as it were, so I don't have to create lots of pages with something like the following in it:

PHP:

	$db = @mysql_query("SELECT * FROM table WHERE id LIKE x");

but rather want to have a dynamic way of showing the data
e.g. /database.php?id=x

where x is the id of one of the entries.

I then want to be able to display the rest of the fields in the same manner, using a template.

Like I said, if anyone could point me in the direction of a decent tutorial that they know of, that would be great! Thanks, Chris.

Jaffa_Cake · 24 May 2007 at 17:31

Code:

$result = mysql_query("SELECT * FROM `table` WHERE `id` = " . quote_smart($_GET['id']));
$row = mysql_fetch_array($result, MYSQL_ASSOC);

template($row['1'], $row['2'], $row['3'], $row['4'], $row['etc']);

function template($row1, $row2, $row3, $row4, $etc) {
?>
<template>
<?php echo $row1; ?>
etc
}

function quote_smart($value)
	{
	    // Stripslashes
	    if (get_magic_quotes_gpc()) {
	        $value = stripslashes($value);
	    }
	    // Quote if not a number or a numeric string
	    if (!is_numeric($value)) {
	        $value = "'" . mysql_real_escape_string($value) . "'";
	    }
	    return $value;
	}

Something along those lines looks good. Have filtered SQL input too

No SQL Injection. Please note I wrote this out into the reply box so there might be a few stupid errors.

herrpoon · 24 May 2007 at 17:42

Many thanks, am going to give this a go now - will let you know how I get on, Chris.

herrpoon · 24 May 2007 at 18:03

I've tried that, but am getting some stupid syntax error, but I don't really understand the following:

Code:

template($row['1'], $row['2'], $row['3'], $row['4']);

function template($row1, $row2, $row3, $row4) {
?>
<template>
<?php echo $row1; ?>
</template>
}

Could you explain it a bit more, I don't understand what row[1] refers to...is that the 1st entry in the database, or one of the fields? Thanks, Chris.

marc2003 · 24 May 2007 at 18:15

i was a little confused too. what is the function for? all you want to display is one record from the database right?

i'd do something like this....

Code:

$id = intval($_GET['id']); //using intval means id has to be a number and validates it as such
$result = mysql_query("SELECT * FROM 'table' WHERE id = '$id'");
if(mysql_num_rows($result) == 1) {
    $row = mysql_fetch_array($result);
    echo $row['columnfromdb1'];
    echo $row['columnfromdb2'];
}

Jaffa_Cake · 24 May 2007 at 18:15

herrpoon said:
Code:

template($row['1'], $row['2'], $row['3'], $row['4']); function template($row1, $row2, $row3, $row4) { ?> <template> <?php echo $row1; ?> </template> }

Could you explain it a bit more, I don't understand what row[1] refers to...is that the 1st entry in the database, or one of the fields? Thanks, Chris.

Sorry I didn't explain it very well, difficult to judge your PHP knowledge.

When the script gets the results it loads the results into an array using this line:
$row = mysql_fetch_array($result, MYSQL_ASSOC);

Your table name may be ID inwhich case the result would be in the array under $row['id']. Your content field or whatever else you have in your sql field would be $row['content'].

You need to replace all these fields to the names of the ones you are going to use, and remove others you don't need.

The template function was designed as a template which you said you would like to use. All the information from the SQL is passed to the template when the function is called which is this line:
template($row['1'], $row['2'], $row['3'], $row['4']);

Again these need to be replaced with the names of the fields in your SQL. A template would contain your HTML and would echo the information from the database where required. Example:

Code:

template($id, $content) {
?>
<body>
The ID is <?php echo $id; ?>.<br />
The content for this ID is: <?php echo $content; ?>
</body>
<?php
}

We must they pass the information to the function which outputs the HTML by adding it to the parameters when calling the function. Example:

Code:

template($row['id'], $row['content']);

Hope you understand what im talking about.

Jaffa_Cake · 24 May 2007 at 18:17

marc2003 said:
i was a little confused too. what is the function for? all you want to display is one record from the database right?

Was adding the template function in to allow the use of the template as he asked for.

The quote_smart function stops people being able to execute SQL injection by calling a url like:

script.php?id=' UNION SELECT `password` FROM `users` //

herrpoon · 24 May 2007 at 18:40

Ooh, I have it working

Here is the working code:

Code:

<?php
// Make a MySQL Connection
mysql_connect("localhost", "username", "password") or die(mysql_error());
mysql_select_db("database_name") or die(mysql_error());

$result = mysql_query("SELECT * FROM `field` WHERE `id` = " . quote_smart($_GET['id']));
$row = mysql_fetch_array($result, MYSQL_ASSOC);

template($row['id'], $row['content']);

function template($id, $content) {
?>

<body>
The ID is <?php echo $id; ?>.<br />
The content for this ID is: <?php echo $content; ?>
</body>
<?php
}
?>

<?php
function quote_smart($value)
	{
	    // Stripslashes
	    if (get_magic_quotes_gpc()) {
	        $value = stripslashes($value);
	    }
	    // Quote if not a number or a numeric string
	    if (!is_numeric($value)) {
	        $value = "'" . mysql_real_escape_string($value) . "'";
	    }
	    return $value;
	}

?>

For the nice SQL injection function at the bottom, is there a way of when people try and inject evil code, or just more innocently, type in an id which doesn't exist, it sends them back to the first id or something similar? At the moment it just displays this:

The ID is .
The cotent for this ID is:

Thanks for your help on this Jaffa cake, most excellent! :cool:

Jaffa_Cake · 24 May 2007 at 20:14

Mate its pretty self explanitory from there.

The template function will output the HTML onto the users browser.

Simply change whats inside the function, change what rows are being taken from SQL etc.

I can't spoon feed you the whole site.

marc2003 · 24 May 2007 at 21:05

Code:

if(mysql_num_rows($result) == 1) {
    //template function
} else {
    //id not found, do something else
}