query about my $query

[ziggi]

Hello everyone,

On the surface this appears quite simple, but alas, I can't suss it and it's doing my head in!

The following script calls and displays information from a mysql table called 'url_titles' once I enter the lastname of an individual (stored in the database) in the search field. That works fine, however I can't seem to retrive and display the data called 'type' stored in a mysql table called 'url_types'.

I've changed my query many times, but I can't seem to access this piece of information so that it can be displayed.

What am I doing wrong!? Can anyone help.

ps: I've made bold the sections that I think are most relevant, but obviously I could be wrong.


<?php # search_urls.php
// This page displays the RoPD listed in the database.
// Set the page title and include the HTML header.
$page_title = 'View URLs';
include_once ('includes/header.html');
require_once ('../mysql_connect.php'); // Connect to the database.
// Create a form allowing the user to select a URL type to view.
echo' <div align="center">
<form method="get" action="view_urls.php">
<select name="type">
<option value="NULL">Select a Year:</option>';
// Retrieve and display the available types.
$query = 'SELECT * FROM urls WHERE type ORDER BY type ASC';
$result = mysql_query ($query);
while ($row = mysql_fetch_array ($result, MYSQL_NUM)) {
echo '<option value="', $row[0], '">', stripslashes($row[1]), '</option>';
}
// Retrieve and display the available types.
$query = "SELECT type FROM url_types WHERE type_id=$t";
$result = mysql_query ($query);
while ($row = mysql_fetch_array ($result, MYSQL_NUM)) {
echo '<option value="', $row[0], '">', stripslashes($row[1]), '</option>';
}
echo '</select>
<input type="submit" name="submit" value="Go!">
</form>';
echo '<p>OR</p>';
//specify how many results to display per page
$limit = 10;
// Get the search variable from URL
$var = @$_GET['q'] ;
//trim whitespace from the stored variable
$trimmed = trim($var);
//separate key-phrases into keywords
$trimmed_array = explode(" ",$trimmed);
// check for an empty string and display a message.
if ($trimmed == "last_name") {
$resultmsg = "<p>Search Error</p><p>Please enter a search...</p>" ;
}
// check for a search parameter
if (!isset($var)){
$resultmsg = "";
}
// Build SQL Query for each keyword entered
foreach ($trimmed_array as $trimm){

// EDIT HERE and specify your table and field names for the SQL query
$query = "SELECT * FROM url_titles WHERE last_name LIKE \"%$trimm%\" ORDER BY last_name DESC" ;
// Execute the query to get number of rows that contain search keywords
$numresults=mysql_query ($query);
$row_num_links_main =mysql_num_rows ($numresults);
// next determine if 's' has been passed to script, if not use 0.
// 's' is a variable that gets set as we navigate the search result pages.
if (empty($s)) {
$s=0;
}
// now let's get results.
$query .= " LIMIT $s,$limit" ;
$numresults = mysql_query ($query) or die ( "Couldn't execute query" );
$row= mysql_fetch_array ($numresults);
//store record id of every item that contains the keyword in the array we need to do this to avoid display of duplicate search result.
do{
//EDIT HERE and specify your field name that is primary key
$adid_array[] = $row[ 'type_id' ];
}while( $row= mysql_fetch_array($numresults));
} //end foreach
if($row_num_links_main == 0 && $row_set_num == 0){
$resultmsg = "<p><b>Search results for:" . $trimmed ."</b></p><p>Sorry, your search returned zero results</p>" ;
}
//delete duplicate record id's from the array. To do this we will use array_unique function
$tmparr = array_unique($adid_array);
$i=0;
foreach ($tmparr as $v) {
$newarr[$i] = $v;
$i++;
}
// now you can display the results returned. But first we will display the search form on the top of the page
?>
<form action="search.php" method="get" name="search">
<div align="center">Enter Last name:
<input name="q" type="text" value=" <?php echo $q; ?> " size="15">
<input name="search" type="submit" value="Search">
</div>
</form>
<?php
// display what the person searched for.
if( isset ($resultmsg)){
echo $resultmsg;
exit();
}else{
echo "Search results for: " . $var;
}

echo "<hr /><div align=\"center\"><b>$type Records</b><br />
<small>(Records are listed numerically starting with RoPD: 00-01.)</small></div>\n";
$first = TRUE; // Initialize the variable.
$result = mysql_query ($query);
// Display all the URLs.
while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) {


if ($first) {
echo '<table border="0" width="100%" cellspacing="5px" cellpadding="0" align="center">
<tr>
<td align="right" width="50%"><font size="4">Categories/</font></td>
<td align="left" width="50%"><font size="4">Record of Professional Development</font></td>
</tr>';
} // End of $first IF.

// Display each record.
echo " <tr>
<td>&nbsp;</td>
</tr>
<td align=\"right\"><p><b>Title:</b></p></td>
<td align=\"left\">{$row['title']}</td>
</tr>
<tr>
<td align=\"right\"><p><b>First Name:</b></p></td>
<td align=\"left\">{$row['first_name']}</td>
</tr>
<tr>
<td align=\"right\"><p><b>Last Name:</b></p></td>
<td align=\"left\">{$row['last_name']}</td>
</tr>
<tr>
<td align=\"right\"><p><b>Qualifications:</b></p></td>
<td align=\"left\">{$row['description']}</td>
</tr>
<tr>
<td align=\"right\"><p><b>Position:</b></p></td>
<td align=\"left\">{$row['position']}</td>
</tr>
<tr>
<td align=\"right\"><p><b>Start date:</b></p></td>
<td align=\"left\">{$row['start_date']}</td>
</tr>
<tr>
<td align=\"right\"><p><b>Whole School Training:</b></p></td>
<td align=\"left\">{$row['whole_school_training']}</td>
</tr>
<tr>
<td align=\"right\"><p><b>Subject Specific Training:</b></p></td>
<td align=\"left\">{$row['subject_specific_training']}</td>
</tr>
<tr>
<td align=\"right\"><p><b>Coaching and Mentor Training:</b></p></td>
<td align=\"left\">{$row['mentor_training']}</td>
</tr>
<tr>
<td align=\"right\"><p><b>ITT Involvement:</b></p></td>
<td align=\"left\">{$row['itt_involvement']}</td>
</tr>
<tr>
<td align=\"right\"><p><b>Other Training:</b></p></td>
<td align=\"left\">{$row['other_training']}</td>
</tr>
<tr>
<td align=\"right\"><p><b>Year of Training:</b></p></td>
<td align=\"left\">{$row['type']}</td>
</tr>
\n";

$first = FALSE; // One record has been returned.

//} // End of while loop.
// If no records were displayed...

foreach($newarr as $value){

// EDIT HERE and specify your table and field names for the SQL query
$query_value = "SELECT * FROM url_titles WHERE title_id = '$last_name'";
$num_value=mysql_query ($query_value);
// $row_last_name= mysql_fetch_array ($num_value);
$row_num_links= mysql_num_rows ($num_value);

} //end foreach $trimmed_array
if($row_num_links_main > $limit){
// next we need to do the links to other search result pages
if ($s>=1) { // do not display previous link if 's' is '0'
$prevs=($s-$limit);
echo "<div align='left'><a href='$PHP_SELF?s=$prevs&q=$var&catid=$catid'>Prev ious " .$limit. "</a></div>";
}
// check to see if last page
$slimit =$s+$limit;
if (!($slimit >= $row_num_links_main) && $row_num_links_main!=1) {
// not last page so display next link
$n=$s+$limit;
echo "<div align='left'><a href='$PHP_SELF?s=$n&q=$var&catid=$catid'>Next " .$limit. "</a></div>";
}
}
} //end foreach $newarr
// If no records were displayed...
if ($first) {
echo '<div align="center">There are currently no records in this category.</div>';
} else {
echo '</table>'; // Close the table.
}
mysql_close(); // Close the database connection.
include_once ('includes/footer.html'); // Require the HTML footer.
?>

[ioncube]

Where do you initialise the value for $t?

[nate2099]

Code: Select all

"SELECT type FROM url_types WHERE type_id='$t'";

Have you tried this??

[ioncube]

type_id is presumably a numeric value or alphanumeric value, so quotes shouldn't be necessary although they wouldn't hurt either. $t is not set, but from seeing $s further down, I suspect that there is a reliance on register_globals being set, which is a very bad idea for security as well as making the code obscure.

Because the code has no form of error checking on the queries, any problem in the query formation is not revealed.

Using a function to perform queries that wraps mysql_query() is a good place to start so that you can keep your query calls simple and just one liners while easily add error checking, turning on or off echoing or logging of queries, adding timing and so on inside the query function without having to change the main code.

Unrelated, but using PHP's equivalent of a here document with <<< (yes, 3 < symbols) for the big echo statement would avoid the need to tediously escape the double quotes while still allowing variable substituion.

[c010depunkk]

The code is a bit strange. Try this:

PHP: Select all

// Retrieve and display the available types.
 $query = mysql_query("SELECT type FROM url_types WHERE type_id='".$t."'");
 while ($row = mysql_fetch_object ($query)) {
 echo '<option value="', $row[0], '">', stripslashes($row[1]), '</option>';
 } 

And use CODE or PHP tags next time when you are displaying source code.

[nate2099]

I suspect that there is a reliance on register_globals being set, which is a very bad idea for security as well as making the code obscure.

Hi Ioncube,

Could you explain this a little further for my benefit!!

Thanks,

Nate.

[ioncube]

mysql_fetch_object() returns a class instance rather than an array with numeric indices, and so $row[0] should be $row->type etc.

Both mysql_fetch_object and mysql_fetch_assoc are preferable to mysql_fetch_row as the code that accesses the result set is clearer. $row->type is less characters to type than $row['type'] and so the object form over the array may be advantageous for that reason.

type_id='".$t."'" is more effort to type and also harder to read than type_id='$t'" and so the latter may be preferable for that reason.

[ioncube]

Nate

The register_globals feature in PHP is where PHP globals automagically get set based on information passed in via POST or GET. This is a neat trick as it saves writing code to access $_POST or $_GET, but by giving the visitor a way to set the initial values for any global variables can cause code to malfunction if it's possible to bypass the places where the globals should get set before they are used.

If you're writing code for use on your own server where hopefully people have no access to the source to discover an exploit, and where you're not shipping it to random servers that may or may not have register globals enabled, then it's unlikely to be a big deal. But it's good practice to disable register_globals in the php.ini file (and more recent versions of PHP ship with a comment in the default php.ini file to that effect) and to access $_POST or $_GET explicitly. It's also good practice to avoid the use of globals as much as possible without doing so just for the sake of doing so.

[nate2099]

So if I POST a variable from a form, and read it on the next page

Code: Select all

$t = $_POST['t'];

then $t will be set for every page thereafter??

[ioncube]

It depends what you mean by every page thereafter, but $t will be set from that point on in the code unless changed and until the script ends. One web request knows nothing about values set in a previous request because the web server is stateless.

This is where sessions come in useful, with a cookie or GET parameter having an ID that is associated with a collection of data that is stored persistently somewhere, such as in a file, in shared memory, in a database etc. The session data provides a way of preserving state between one page request and another if this is what you need to do.

Another way to preserve state is via hidden fields in a form, where you render a form having hidden fields with values that you want to access again when the form is posted. Depending on what the hidden field data represents, there's a potential security issue with that approach though because a form POST could be made with different hidden field data to what was supposed to be there. A technique to reduce the risk of that is to have a hidden field containing a checksum of the hidden field data that is computed when generating the form, and then verified when the form is submitted.

With your code example you need to decide how $t is going to be set, and presumably it's from $_POST as with some of the other variables, and I suspect that it simply wasn't being set and so the query was invalid. var_dump() can be a crude by useful debugging technique for dumping out the value and types of variables when strange behaviour is going on. So that the output is easier to read, I usually have a function called dump_var() defined as something such as:

Code: Select all

function dump_var($v)
{
  echo '<pre>';
  var_dump($v);
  echo '</pre>';
}