MySQL and PHP CRUD simple listing

whitecrook

whitecrook

whitecrook

Soldato
Joined
22 Aug 2005
Posts
8,971
Location
Clydebank
Hi.

I've got a simple crud system working for a simple (1 table, 3 column) database i have setup using PHP and mysql.

This is based on 4 files, new.php, edit.php, list.php and delete.php

Now I have list.php working nicely, pagination, records per page etc for a simple hard coded "SELECT * FROM `main_table` ORDER BY id DESC LIMIT $start_from, $records_per_page" but I now I am trying to write some reporting functionality and I would like to simply use the same list.php to list the results of the mySQL query. I have my user choose the reports from another page named report.php

But the question is how do I get different mysql queries into the list.php file? As far as I can see the main methods of passing info are via GET and POST.

Is it really sensible to POST the SQL query to the list.php form? Is this how it's done?


Cheers all.
 
You don't generally post an entire SQL query, as that's exceptionally vulnerable to SQL injection.
Usually you post the variables eg;
PHP: 
$sql = "SELECT * FROM users WHERE UID='".$_POST['uid']."'"
But that's also prone to sql injection (which could in theory delete the entire DB!) so you need to sanitize the information you're passing with a php function such as htmlentities() or mysql_real_escape_string() (or better yet both!)
 
Martins said:
But that's also prone to sql injection (which could in theory delete the entire DB!) so you need to sanitize the information you're passing with a php function such as htmlentities() or mysql_real_escape_string() (or better yet both!)
Click to expand...

I agree, it's probably not necessary to pass the SQL string in it's entirety, only certain aspects of it, e.g. the ASC or DESC for sort direction. If you know that the relevant values are only going to be say ASC or DESC then it's easy to write a validation routine that will only allow these values, and will exit the script if anything else is tried, e.g. if someone is trying an SQL injection.

Rgds
 
radderfire said:
I agree, it's probably not necessary to pass the SQL string in it's entirety, only certain aspects of it, e.g. the ASC or DESC for sort direction. If you know that the relevant values are only going to be say ASC or DESC then it's easy to write a validation routine that will only allow these values, and will exit the script if anything else is tried, e.g. if someone is trying an SQL injection.

Rgds
Click to expand...

or even better pass a reference to the sql in querystring E.g. index.php?s=pricea

Then change the sql depedning on what the querystring data is.
 
suarve said:
or even better pass a reference to the sql in querystring E.g. index.php?s=pricea

Then change the sql depedning on what the querystring data is.
Click to expand...

Yeah, that's a better aproach, hard coding 'ASC' and 'DESC' into your code is a much better idea than trying to sanitise user input to get the same values, any time you are filtering user input to generate a query string your asking for trouble.
 
OK.. thanks for the replies

So My list.php file would have at the top, a bunch of if then (or case) statements, and would take the value i pass - e.g. if (isset $_POST['report1']) then set $sql="SELECT etc etc " ?

and so on for different reports.

I shall post up some code, but I was trying something similar, but I couldn't get it to assign the query to a varible which I later then inserted . e.g:

At the start of this code section I assign a value to $sql_query - if I substitute this line : $result1 = mysql_query("SELECT COUNT(id) FROM `main_table`") or trigger_error(mysql_error());

with something like or a variation of : $result1 = mysql_query($sql_query) or trigger_error(mysql_error());

I get errors....

"
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\xampp\htdocs\crud\simple_crud\list.php on line 34"



Any help? ?

Cheers all

PHP: 
<? 
include('config.php');
// if (!isset($_POST['rpp'])) { $records_per_page=30; } ;
if (isset($_POST['submitted'])) { 
		$records_per_page= $_POST['rpp'];
	} else {
	if (isset($_GET["rpp"])) { $records_per_page  = $_GET["rpp"]; } else { $records_per_page=30; }; 
};
if ($records_per_page==0) { echo "You asked for 0 records per page - You are getting 30"; $records_per_page=30; } ;

$sql_query="SELECT * FROM `main_table` ORDER BY id DESC LIMIT $start_from, $records_per_page";
	

$result1 = mysql_query("SELECT COUNT(id) FROM `main_table`") or trigger_error(mysql_error()); 
$row = mysql_fetch_row($result1); 
$total_records = $row[0]; 
$total_pages = ceil($total_records / $records_per_page);

if (isset($_GET["page"])) { $page  = $_GET["page"]; } else { $page=1; }; 

if ( $total_pages<$page ) { $page = $total_pages; };



$start_from = ($page-1) * $records_per_page; 
echo "<table border=1 >"; 
echo "<tr>"; 
echo "<td><b>Id</b></td>"; 
echo "<td><b>App No</b></td>"; 
echo "<td><b>Council</b></td>"; 
echo "<td><b>Date</b></td>"; 
echo "</tr>"; 
$result = mysql_query("SELECT * FROM `main_table` ORDER BY id DESC LIMIT $start_from, $records_per_page") or trigger_error(mysql_error()); 
while($row = mysql_fetch_array($result)){ 
foreach($row AS $key => $value) { $row[$key] = stripslashes($value); } 
echo "<tr>";  
echo "<td valign='top'>" . nl2br( $row['id']) . "</td>";  
echo "<td valign='top'>" . nl2br( $row['app_no']) . "</td>";  
echo "<td valign='top'>" . nl2br( $row['council']) . "</td>";    
echo "<td valign='top'>" . nl2br( $row['date']) . "</td>";
echo "<td valign='top'><a href=edit.php?id={$row['id']}>Edit</a></td><td><a href=delete.php?id={$row['id']}>Delete</a></td> "; 
echo "</tr>"; 
} 
echo "</table>"; 
echo "<a href=new.php>Add Application</a><P>"; 


 

echo "Page: ";
for ($i=1; $i<=$total_pages; $i++) { 
            echo "<a href=list.php?page=".$i."&rpp=".$records_per_page.">".$i."</a> "; 
};
?>
<form action='' method='POST'> 
<p>Records Per Page: <input type='text' name='rpp' value='30'/>  
<input type='submit' value='Update' /><input type='hidden' value='1' name='submitted' /> 
</form>
 
Just a tip to help you debug, assign the sql query string to a variable before executing the query with mysql_query() and print the query before you execute it, this will let you see if you have the sytax correct for example:

$querystring = 'SELECT * FROM table'
print($querystring);
$res = mysql_query($querystring);

Obviously you take this out when you get the code working but it can help you debug if you get strange results. You can test the query in the mysql console as well to check the results are as expected.

Launch the console:

Start > Run > cmd

cd c:\xampp\mysql\bin\

mysql.exe -u root -p

This will log you into the server the root user and ask you for the password, change as neccessary.

From the mysql prompt type:

USE <database_name>

This will select the databse to use, from here you can then issue sql statements (such as the one printed from your script) to test if they produce the valid result.
 
Right guys thanks

Think I got it..

Couple of things...

I was declaring the SQL query (with the variables in it) BEFORE I had assigned a value to a variable ($start_from) I take it PHP will not 'retroactively' assign a variable ...

e.g. if i say :

PHP: 
$test1 = 4
$test = $test1+$test2
$test2 = 5

echo $test ....  ?? i.e. that above is wrong...

Also, My query should have looked like this:

PHP: 
$querystring='SELECT * FROM main_table ORDER BY id DESC LIMIT ' . $start_from .', ' . $records_per_page ;

and not like

PHP: 
$querystring='SELECT * FROM main_table ORDER BY id DESC LIMIT $start_from, $records_per_page';


... OK I'll keep working on this thing.... but for now I think I now know how to sub in a SQL query using a variable which also contains evaluated variables.


Cheers all
 
You must log in or register to reply here.
Back
Top Bottom