MySQL and PHP CRUD simple listing

[whitecrook]

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.

 

[whitecrook]

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

<? 
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>

 

[whitecrook]

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 :

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

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

Also, My query should have looked like this:

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

and not like

$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