Conditional SQL Queries

[stragglerat]

I'm using PHP to pull items from a SQL DB.  Is there a way to make one distinct item page and have a PHP script plug in an individual field entry to query just that item? That way a user can choose an item from a list, click "Details" and see a page with just that item. I know, this is confusing.  I'm just trying to keep from having to make a page for every item I have. Any ideas?

[phpzone]

You may need to elaborate a little on what you are trying to achieve, but I'll have a stab at answering your question.

 

The following will show a projects details, in a template, showing details for the project chosen by the user, if that's not what you meant, please

give more details as to what you are trying to achieve

 

TABLE projects:

 

  project_id                  INT UNSIGNED

  project_name            VARCHAR(80)

  project_description    TEXT

 

project_selector.php file:

<?php

   // put your mysql connection code here to connect to the DB

   if ( isset($_POST['smbt']) )
   {
            $project_id =@ (int)$_POST['project_id'];  // cast to int so we will always get an integer

            $sql = "SELECT * FROM projects WHERE project_id = {$project_id}";

            $ds = mysql_query( $sql ) or trigger_error( mysql_error(), E_USER_ERROR );

            $data = mysql_fetch_array( $ds, MYSQL_ASSOC );

            $project_name = htmlspecialchars( $data['project_name'], ENT_QUOTES );

            $project_description = htmlspecialchars( $data['project_description'], ENT_QUOTES );

            print <<<_TEMPLATE

                <p>Project Name: {$project_name}</p>

                <p>Description:<br />{$project_description}</p>

_TEMPLATE;
   }
   else
   {

          $options = '';

          $sql = "SELECT project_id, project_name FROM projects ORDER BY project_name ASC";

          $ds = mysql_query( $sql ) or trigger_error( mysql_error(), E_USER_ERROR );

          while ( $data = mysql_fetch_array( $ds, MYSQL_ASSOC ) )
          {

                $options .= '<option value="' . $data['project_id'] . '">' . htmlspecialchars($data['projects_name'], ENT_QUOTES) . '</option>';

          }

          print <<<_FORM

            <h1>Please choose a project:</h1>
         
           <form action="{$_SERVER['PHP_SELF']}" method="post">         

            <select id="project_id" name="project_id">
                     {$options}
            </select>

           <input type="submit" name="sbmt" value="Show Project" />

            </form>

_FORM;

   }

?>

 

PS. I've actually typed all this code in, not tested so there may be some errors but I quickly scanned it afterwards, should be ok 

 

[stragglerat]

Thanks for your help. Reading your post made me think about using a form to post data to the item page. Having some trouble though. Here's the form code from the main page:

 

echo "<form action='details.php' method='post'><input type='image' src='images/button_details.gif' name='0' value='".$row[0]."'></form>";

 

Here's the relevant part of details.php:

 

$query = "SELECT * FROM wwjitems WHERE item = '"$_POST["0"]"'";

 

And it's returning this error:

 

Parse error: parse error, unexpected T_VARIABLE in /home/content/s/t/r/stragglerat/html/details.php on line 402

 

which is referring to that query up there. I believe it's a syntax error.  What do you think?

[stragglerat]

Lol, rookie mistake. Forget to put a . before and after. It works now. Thanks for your help, man!

[phpzone]

No probs, you could have written either ot the following, but also you need security on this

 

    $query = "SELECT * FROM wwjitems WHERE item = '" . $_POST["0"] . "'";

 

or

 

    $query = "SELECT * FROM wwjitems WHERE item = '{$_POST["0"]}'";

 

 

If your variable in $_POST["0"] (better to use a descriptive name in there btw) should always be an integer do this:

 

$post0 = (int)$_POST["0"];

 

This will force the value to be an integer and help mitigate hacking attempts.

 

You should also always escape any text when querying eg:

 

    $name = $_POST['name'];

    // should be connected to mysql before the following:

     $db_name = mysql_real_escape_string( $name );  

     $sql = "SELECT * FROM people WHERE name = '" . $db_name .  "'";

 

If you don't escape queries, you will fall into the beginners trap of leaving your database queries wide

open for hackers.

 

 

[stragglerat]

Hmm, I never really considered any kind of security measures.  I have to go to work right now, but I'd definitely like to talk to you more about this, and I have a question about your previous post.  Is there a way I can contact you directly, maybe through e-mail or IM? Unless, of course you'd like to keep the convo here to help others with the same problem. Thanks again.