First thing I'll say is, I'm very comfortable with HTML, an intermediate PHP coder, and a beginner at SQL.

 

Now, this is what I have functioning at the moment:

Function that reads the page= variable in the URL, and displays "content/$page.php", but if it doesn't exist, it shows a 404.

 

$dyn_page = isset($_GET['page']) ? $_GET['page'] : "home";
function show_content($dyn_page){
$dyn_file = file_exists("content/$dyn_page.php") ? "content/$dyn_page.php" : "content/404.php";
require_once($dyn_file);
}

 

Now, this is what I want to have functioning:

Function that reads the page= variable in the URL, and calls upon an SQL database to echo "database name > pages > $page > content", but if it doesn't exist, it shows a 404. And a way to show a textbox to edit "database name > pages > $page > content". Then a way to generate a table row showing "database name > pages > (all) > title".

 

Sorry if that sounds a bit complicated, lol.

calls upon an SQL database to echo "database name > pages > $page > content", but if it doesn't exist, it shows a 404.

...

to edit "database name > pages > $page > content"

...

showing "database name > pages > (all) > title"

I'm confused by what the > means in each case? I don't think it's standard notation. I guess you mean:

• the database is called "db" (for example)
  
• the table inside that database is called "pages"
  
• this table has a field which is going to be mapped to the $page value (you don't mention a name for this field?)
  
• each record also has a field called "content" and another called "title" (the latter is obvious, but I'm not sure what the former actually is for?)
  

Before going any further and getting all the SQL wrong, is that correct? If not, can you explain more fully? If you could give the database schema/table structure which already exists (or your plans for one if it doesn't yet exist), that would help.

That is pretty much correct.

 

So if I were to go "www.example.com/index.php?page=home" it would call the database, then show table: pages, row: home, field: content. I'm really not sure about the row part. :s I'm not too good with SQL yet.

OK. So you have a table ("pages") with these fields:

 

page VARCHAR(64) PRIMARY KEY

content TEXT

title VARCHAR(256)

 

I think those field types will be appropriate. You might need a larger capacity for content (like MEDIUMTEXT or LONGTEXT). You might also want a title longer than 256 characters, though I doubt it. I'll also assume your database is called "mydb".

 

Then in PHP:

 

$db = mysql_connect($server,$username,$password); // put the right values/variables in here
$page = mysql_escape_string($_GET['page']); // prevents SQL injection attacks
$resultSet = mysql_query("SELECT content FROM mydb.pages WHERE page='$page'",$db); // do query
$firstRow = mysql_fetch_row($resultSet); // get the first row, or FALSE if no rows available
if($firstRow === FALSE) {
 /* No result, so do the 404 */
 // TODO 404
} else {
 $content = $firstRow[0]; // [0] is the first entry, so is the value of the 'content' field in this row
 // TODO display $content
}

It's not working for me. :\ Here's what I got:

 

function new_show_content(){
$db = mysql_connect(*,*,*); // put the right values/variables in here
$page = mysql_escape_string($_GET['page']); // prevents SQL injection attacks
$resultSet = mysql_query("SELECT content FROM mydb.pages WHERE page='$page'",$db); // do query
$firstRow = mysql_fetch_row($resultSet); // get the first row, or FALSE if no rows available
if($firstRow === FALSE) {
	/* No result, so do the 404 */
	include("content/404.php");
} else {
	$content = $firstRow[0]; // [0] is the first entry, so is the value of the 'content' field in this row
	echo $content;
//	}
}

Obviously the code comes from my head without warranty and isn't bug-checked... it is likely to have errors which you need to debug for yourself It's very difficult to say why it isn't working. You could try setting error_reporting(E_ALL) and see what happens? A daft question, but have you created the "mydb" database and "pages" table?

Yeah I've done all the SQL stuff, I'll have another look at this tonight and see what I can do with it.

Ok I've finally gotten back to this, lol. I think I'm really close to the working code right now. It's connecting to the database successfully, the $dyn_page variable is working fine, there aren't any typos, so I've put it up to something being wrong with the execution. Because it doesn't show anything.

 

// in config.php
$db_host	=	"***"; # host
$db_name	=	"***"; # database name
$db_user	=	"***"; # username
$db_pass	=	"***"; # password

// in functions.php
$db_connect	= mysql_connect($db_host,$db_user,$db_pass);
mysql_select_db($db_name,$db_connect);
$dyn_page = isset($_GET['page']) ? $_GET['page'] : "home";
function show_content(){
$query = sprintf("SELECT content FROM a_main WHERE short='$dyn_page'", mysql_real_escape_string($dyn_page));
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
	echo $row['content'];
}
mysql_free_result($result);
}

// in index.php
require_once("config.php");
require_once("functions.php");
show_content($dyn_page);

 

Also, could someone confirm that this code is secure? Or could that be easily exploited?

Surely the sprintf line should read:

$query = sprintf("SELECT content FROM a_main WHERE short='%s'", mysql_real_escape_string($dyn_page));

using %s in the format string? Then it'll be SQL injection-proofed. It's not obvious why this isn't working though. Is the show_content() function definitely being called?

Thanks for that, and the function is definitely being called, otherwise I wouldn't have been able to debug the database connection and query.

 

EDIT: I found a solution! It was the variable in the query that it didn't like, it wasn't parsing the variable it was actually looking for $dyn_page in the table.

 

// in config.php
$db_host	=	"***"; # host
$db_name	=	"***"; # database name
$db_user	=	"***"; # username
$db_pass	=	"***"; # password

// in functions.php
$db_connect = mysql_connect($db_host,$db_user,$db_pass);
mysql_select_db($db_name,$db_connect);
$dyn_page = isset($_GET['page']) ? $_GET['page'] : "home";
function show_content($dyn_page){
$query = sprintf("SELECT content FROM a_main WHERE short='%s'", mysql_real_escape_string($dyn_page));
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
	echo $row['content'];
}
mysql_free_result($result);
}

// in index.php
require_once("config.php");
require_once("functions.php");
show_content($dyn_page);

 

Now I just got to get a 404 function happening.

Ok now I'm having trouble testing if the record exists, and it's really giving me the ****s. If any more help is available it would be much appreciated.

*bump*

Ok people not to worry, I finally got it to configurez.

 

// in config.php
$db_host	=	"***"; # host
$db_name	=	"***"; # database name
$db_user	=	"***"; # username
$db_pass	=	"***"; # password

// in functions.php
$db_connect = mysql_connect($db_host,$db_user,$db_pass);
mysql_select_db($db_name,$db_connect);
$dyn_pvar = isset ($_GET['p']) ? $_GET['p'] : "home";

function dyn_content($dyn_pvar){
$dyn_query = sprintf("SELECT * FROM a_main WHERE short='%s'", mysql_real_escape_string($dyn_pvar));
$dyn_result = mysql_query($dyn_query);
$dyn_found = (boolean) ($dyn_result) ? mysql_num_rows($dyn_result) : FALSE;
if ($dyn_found == 0) {
	$dyn_query = sprintf("SELECT * FROM a_main WHERE short='%s'", mysql_real_escape_string("404"));
	$dyn_result = mysql_query($dyn_query);
}
while ($row = mysql_fetch_assoc($dyn_result)) {
		echo $row['content'];
}
mysql_free_result($dyn_result);
}

// in index.php
require_once("config.php");
require_once("functions.php");
show_content($dyn_page);

I have no idea why it wouldn't work before, but all we need to know is that it works now.