generate query from URL

jacko_162 · April 4, 2010

I want to pass information via a URL somethink along the following;

mypage.php?mytank=3

where 3 is the "tank_id" of the database field.

i have the following code which works and pulls ALL the information, how can i restrict this to only pull down where tank_id = that passed in the URL;

<?php
session_start();

include('Includes/auth.php');
require_once('header.php');
require_once('Includes/phpFreaksCrypto.class.php4'); // require the phpFreaksCrypto class
?>
<?

$connect = @mysql_connect("$host", "$username", "$password");

if (!($connect)) // If no connect, error and exit().
{
echo("<p>Unable to connect to the database server.</p>");
exit();
}

if (!(@mysql_select_db($database))) // If can't connect to database, error and exit().
{
echo("<p>Unable to locate the $database.</p>");
exit();
}

if (!($limit)){
$limit = 8;} // Default results per-page.
if (!($page)){
$page = 0;} // Default page value.
$numresults = mysql_query("SELECT * FROM tests WHERE member_id=$_SESSION[sESS_MEMBER_ID]"); // the query.
    $decoded = $ID;
$crypto = new phpFreaksCrypto(); 
    $id_to_be_encrypted = $ID; 
    $id_encrypted = $crypto->encrypt($id_to_be_encrypted); 
    $id_decrypted = $crypto->decrypt($decoded); 
    $crypto->__destruct();

$numrows = mysql_num_rows($numresults); // Number of rows returned from above query.
if ($numrows == 0){
include('notests.php'); // bah, modify the "Not Found" error for your needs.
exit();}

$pages = intval($numrows/$limit); // Number of results pages.

// $pages now contains int of pages, unless there is a remainder from division.

if ($numrows%$limit) {
$pages++;} // has remainder so add one page

$current = ($page/$limit) + 1; // Current page number.

if (($pages < 1) || ($pages == 0)) {
$total = 1;} // If $pages is less than one or equal to 0, total pages is 1.

else {
$total = $pages;} // Else total pages is $pages value.

$first = $page + 1; // The first result.

if (!((($page + $limit) / $limit) >= $pages) && $pages != 1) {
$last = $page + $limit;} //If not last results page, last result equals $page plus $limit.

else{
$last = $numrows;} // If last results page, last result equals total number of results.

//escape from PHP mode.
?>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf8"/>
<title>Index</title>
</head>

<body class="cloudy">
<table width="60%" border="0" align="center" cellpadding="2" cellspacing="2">
      <tr>
        <td width="40%" valign="top">
        <div align="right"><a href="addtest.php"><img src="img/buttons/add_results.png" alt="" /><img src="img/spacer.png" alt="" width="10" height="1" /></a></div>
        <div class="content-box column-left">
	<div class="content-box-header">
				<table width="100%" border="0" cellspacing="2" cellpadding="4">
  <tr>
    <td width="80%" valign="middle"><h3>My Results  » All</h3></td>
    <td width="20%" align="right" valign="middle"><FORM name="guideform"> <select name="tank" onChange="window.location=document.guideform.tank.options[document.guideform.tank.selectedIndex].value">>
                          <option value="All" selected="selected"> == Select Tank === </option>
                          <?  
// Query to pull information from the "catagory" Database  
$result = mysql_query("select * from tanks WHERE member_id = '$_SESSION[sESS_MEMBER_ID]' order by id ASC");  
while ($row = mysql_fetch_object($result)){  
?>
                          <option value="tests_tank.php?mytank=<?php echo $row->id; ?>"> <?php echo $row->description; ?> </option>
                          <? }?>
                                </select></FORM></td>
  </tr>
</table>
	  </div><div class="content-box-content"><div><h4></h4>
                  
				<table width="100%" border="0" cellpadding="0" cellspacing="4">
      <tr>
        <td align="center" valign="top"><div><div>
        <h2>Showing 
						  <strong><?=$first?></strong>
						 - 
						<strong><?=$last?></strong>
						 of 
						<strong><?=$numrows?></strong></h2>
</div>
		  <table cellspacing="5" cellpadding="0">
<?php
$query = "SELECT * FROM tests WHERE member_id='$_SESSION[sESS_MEMBER_ID]' ORDER BY id DESC LIMIT $page, $limit";
$result = mysql_query($query) or die("There was a problem with the SQL query: " . mysql_error()); 
if($result && mysql_num_rows($result) > 0)
{
    $i = 0;
    $max_columns = 4;
    while($row = mysql_fetch_array($result))        
   {
       // make the variables easy to deal with
       extract($row);

       // open row if counter is zero
       if($i == 0)
          echo "<tr>";

       // reduces month output data to 3 digits
       $month=substr($month,0,3);

       // make sure we have a valid output
       if($id != "" && $id != null)
          echo "<td width='120' height='115' background='img/blank_calendar.png'><a class='cal_text' href='testresults.php?ID=$id'><div align='center'><strong><img src='img/spacer.png' width='1' height='6'><br>
      $day<br><br>$month</strong></div></a>";
    
       // increment counter - if counter = max columns, reset counter and close row
       if(++$i == $max_columns) 
       {
           echo "</tr>";
           $i=0;
       }  // end if 
   } // end while
} // end if results

// clean up table - makes your code valid!
if($i < $max_columns)
{
    for($j=$i; $j<$max_columns;$j++)
        echo "<td> </td>";
}
?>
</tr>
</table>					  
              <div align="center"><br>
                <?php									  
if ($page != 0) { // Don't show back link if current page is first page.
$back_page = $page - $limit;
echo("<a href=\"$PHP_SELF?query=$query&page=$back_page&limit=$limit\">« Previous</a>    \n");}
for ($i=1; $i <= $pages; $i++) // loop through each page and give link to it.
{
$ppage = $limit*($i - 1);
if ($ppage == $page){
echo("<b>$i</b> \n");} // If current page don't give link, just text.
else{
echo("<a href=\"$PHP_SELF?query=$query&page=$ppage&limit=$limit\">$i</a> \n");}
}
if (!((($page+$limit) / $limit) >= $pages) && $pages != 1) { // If last page don't give next link.
$next_page = $page + $limit;
echo("<a href=\"$PHP_SELF?query=$query&page=$next_page&limit=$limit\">Next »</a>\n");}
?>
              </div>
        </div></td>
      </tr>
    </table>
        </td>
      </tr>
</table>
<?php
require_once('footer.php');
?>
</body>
</html>

i must be something simple which i keep doing wrong.

i assume i have to edit line 28:

$numresults = mysql_query("SELECT * FROM tests WHERE member_id=$_SESSION[sESS_MEMBER_ID]");

and put a "AND tank_id = $mytank" but it still isnt pulling it down?

any help appreciated.

the182guy · April 4, 2010

This should do it

$mytank = (int)$_GET['mytank']; // get the ID from the url and cast it as an integer

$numresults = mysql_query("SELECT * FROM tests WHERE member_id=$_SESSION[sESS_MEMBER_ID] AND tank_id = '$mytank'");

Then the URL would be mypage.php?mytank=3

If tank_id is not an integer in your database then you'll need to remove the (int) cast and use a different way of escaping the value like mysql_real_escape_string()

jacko_162 · April 4, 2010

This should do it
$mytank = (int)$_GET['mytank']; // get the ID from the url and cast it as an integer

$numresults = mysql_query("SELECT * FROM tests WHERE member_id=$_SESSION[sESS_MEMBER_ID] AND tank_id = '$mytank'");
Then the URL would be mypage.php?mytank=3

If tank_id is not an integer in your database then you'll need to remove the (int) cast and use a different way of escaping the value like mysql_real_escape_string()

worked like a charm.

thanks

Sign In

generate query from URL

Recommended Posts

jacko_162

Link to comment

Share on other sites

the182guy

Link to comment

Share on other sites

jacko_162

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information