a simple query, I think

[wkilc]

Hello,

 

I have this basic code running on my "index.php" page.  I'm trying to add a feature where I could query the database by adding a simple query to the URL, for example, "index.php?instrument=flute", so it would display only the rows where the instrument is actually a flute.

 

 <?
$sql = "SELECT * FROM bocj WHERE id='".$_GET['id']."'";
    	$result = mysql_query($sql);
    	$data = mysql_fetch_array($result);
  
?>

      	<table>
      	<tr>
        <td>Sponsor</td>
        <td>Student</td>
<td>Instrument</td>
<td>Rank</td>
<td>Comments</td>
    	</tr>
       	
<?
      	$sql = "SELECT * FROM bocj
ORDER BY bocj.$sort";
    	$result = mysql_query($sql);
        while ($data = mysql_fetch_array($result))
    	{
?>
    	<tr>
<td><?=$data['sponsor']?></td>
<td><?=$data['student']?></td>
<td><?=$data['instrument']?></td>
<td><?=$data['rank']?></td>
        <td><?=$data['comments']?></td>
</tr>
        <?  
}  
?>
        </table>

 

I'm a noob, and I've tried many things from many online tutorials, all without much success.

 

Thanks.

 

~Wayne

[Cep]

This is very dangerous,

 

$sql = "SELECT * FROM bocj WHERE id='".$_GET['id']."'";

 

Always validate your raw input data using htmlspecialchars() mysql_real_escape_string() and try to use a switch statement for the possible answers you expect to see with a default for an answer you do not expect to see.

 

If you need an explanation please say so.

[Psycho]

Try this:

 

$sql = "SELECT * FROM bocj WHERE id='".mysql_real_escape_string($_GET['id'])."'";

if (isset($_GET['instrument'])) {
    $sql .= " AND instrument = '".mysql_real_escape_string($_GET['instrument'])."'"
}

[Cep]

Here is an example of what to do,

 

<?php

$db = "Your database connection object or resource";

// Ensure your raw data is escaped
$rawday = htmlspecialchars($_GET['days'], ENT_QUOTES);
$day = mysql_real_escape_string($rawday, $db);

// Validate the answer with what you expect to see
switch ($day) {
case "Sunday":
case "Monday":
case "Tuesday":
case "Wednesday":
case "Thursday":
case "Friday":
	// Do something for a week day
	break;
default:
	// If not a day return some sort of error or message to the user
	echo "You have not entered a day";
}

// Now do your query
$sql = "SELECT * FROM workdays ORDER BY days WHERE today = {$day}";

?>

[wkilc]

Thanks for the replies!

 

I had a freelancer work on the code for me... in the first post I was attempting to simplify it in order to make it easy to look at, perhaps I took out something important. 

 

It's s simple table (protected by .htaccess) where folks can update one of the table values (rank).  I would like to be able to run a query that would allow me to display on records of a particular instrument, or a particular sponsor, etc.

 

Here's the complete code:

 

<?
    if (isset($_POST['updatestudentbt']))
{
	if ($_POST['rank'] != '')
	{
		if (mysql_query("UPDATE bocj SET `rank` = '".$_POST['rank']."' WHERE `id`= '".$_POST['id']."'"))
			echo "Student was successfully updated!<br /><br />";
		else
			echo "Student was NOT updated!<br /><br />";
	}

}

	if ($_GET['a'] == "edit")
    {
	$sql = "SELECT * FROM bocj WHERE id='".$_GET['id']."'";
    	$result = mysql_query($sql);
    	$data = mysql_fetch_array($result);
?>
    <form action="index.php?p=index" method="post" name="editstudent">
       Name: <? echo $data['student']?>  
<br />
       Instrument: <?=$data['instrument']?>
        <br />
        <br />
       Rank: <input name="rank" type="text" id="rank" size="7" value="<? echo $data['rank']?>" onfocus="if(this.value=='rank?') {this.value='';}" onblur="if(this.value=='rank?') {this.value='';}" />
        <br />
        <br />
        <input type="hidden" name="id" value="<? echo $data['id']?>" />
        <input type="submit" value="Update" name="updatestudentbt" />
      </p>
  </form>
<br />
<a href="index.php?p=index">Go back</a>
<?
}
    else
    {
    ?>


<?
  function pparams($sort) {
    global $sponsor, $school, $student, $instrument;

    echo $HTTP_SERVER_VARS['PHP_SELF'];
    echo "?sponsor=".htmlentities(urlencode($sponsor));
    echo "&student=".htmlentities(urlencode($student));
    echo "&instrument=".htmlentities(urlencode($instrument));
    echo "&sort=".htmlentities(urlencode($sort));
  }

  if (!$sort) $sort="instrument";
?>


      	<table>
      	<tr>
        <td><strong><a href="<? pparams("sponsor"); ?>">Sponsor</a></strong></td>
        <td><strong><a href="<? pparams("student"); ?>">Student</a></strong></td>
<td>dth=<strong><a href="<? pparams("instrument"); ?>">Instrument</a></strong></td></td>
<td><strong><a href="<? pparams("rank"); ?>">Rank</a></strong></td>
<td width="120"><strong>Comments</strong></td>
    	</tr>
       	<?
      	$sql = "SELECT * FROM bocj
ORDER BY bocj.$sort";
    	$result = mysql_query($sql);
        while ($data = mysql_fetch_array($result))
    	{
    	?>
    	 	<tr>
<td><?=$data['sponsor']?></td>
<td><?=$data['student']?></td>
<td><?=$data['instrument']?></td>
<a href="index.php?p=index&a=edit&id=<?=$data['id']?>"><?=$data['rank']?></a>
	</td>
        <td><?=$data['comments']?></td>
	</tr>
        <?}?>
    </table>
<? } ?>

 

Any ideas?

 

~Wayne