Page 1 of 1

Passing Variables with SQL and Php

Posted: Wed Feb 04, 2009 9:29 pm
by llabeoll
Hello. I have tried searching older posts but could not find a threat applicable to my question.

I am setting up a troubleshooting page to get a good jump into php and sql. I am having some trouble, though, passing variables.

I want the visitor to access index.php where they will be greeted with numerous options which will then link to say, troubleshooting.php, downloads.php et cetera.

Now, on troubleshooting.php there will first be a list of software packages pulled from an sql database which are all assigned an id number. Upon clicking on the id number the user is taken to issue.php?softwareis=# and then the issues associated with that software id number are displayed. I am able to pull the software id but I would also like to display the name of the software package as well as a navigation at the top of the page similar to Home > Troubleshooting > Package > Issue and have each then link back to the previous page. Essentially, I am trying to replicate this: http://www.datasystemsassociates.com/dan/aditest/

I am more familiar with asp and access but wish to learn php and sql as well but I am somewhat at a loss. If I should post my code, let me know.

Thank you in advance.

Re: Passing Variables with SQL and Php

Posted: Wed Feb 04, 2009 9:34 pm
by Benjamin
Please post what you have (or a snippet of the part you are stuck on) and let us know where you are stuck. We'll point you in the right direction.

Re: Passing Variables with SQL and Php

Posted: Wed Feb 04, 2009 9:40 pm
by llabeoll
Index.php

Code: Select all

<?php
$con = mysql_connect("SERVER","USERNAME","PASSWORD");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("DBNAME", $con);
$result = mysql_query("SELECT * FROM troubleshooting");
echo "<table border='1'><tr><td>Software Name</td></tr>";
 
while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td><a href = 'issue.php?softwareid=" . $row['softwareid'] . "'>" . $row['softwarename'] . "</a></td></tr>";
  }
echo "</table>";
 
mysql_close($con);
?>
Issue.php

Code: Select all

<?php
$con = mysql_connect("SERVER","USERNAME","PASSWORD");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("DATABASE", $con);
$softwareidvalue = $_GET['softwareid'];
$softwarenamevalue = mysql_query("SELECT `troubleshooting`.`softwarename` FROM troubleshooting WHERE softwareid = $softwareidvalue");
$result = mysql_query("SELECT * FROM softwareissue WHERE softwareid = $softwareidvalue");
 
$row = mysql_fetch_array(softwarenamevalue)
 
echo "<table border='0'><tr><td>" . $row['softwarenamevalue'] . "";
echo "</td></tr>";
 
while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td><a href = 'resolution.php?issueid=" . $row['issueid'] . "'>" . $row['issuename'] . "</a></td></tr>";
  }
echo "</table>";
mysql_close($con);
?>
I am stuck on passing the software name as well as its id number. The softwareid passes fine but I can't seem to get the name to pass, too.

Re: Passing Variables with SQL and Php

Posted: Wed Feb 04, 2009 10:15 pm
by Benjamin
A few things:

The first query should probably be:

Code: Select all

 
$resource = mysql_query("SELECT `softwarename` FROM troubleshooting WHERE softwareid = '$softwareidvalue'");
$row = mysql_fetch_assoc($resource);
$softwarenamevalue = $row['softwarename'];
 
Note that single quotes were added around the variable in the query since it is a string.

Also:

Code: Select all

 
$softwareidvalue = $_GET['softwareid'];
 
Should be:

Code: Select all

 
$softwareidvalue = mysql_real_escape_string($_GET['softwareid']);
 
Which protects your queries from hacking attempts.

Re: Passing Variables with SQL and Php

Posted: Thu Feb 05, 2009 10:30 am
by llabeoll
I have this code now for my issue.php and where I would like my software name to display, I am receiving just the word "array".

My table layout is as follows:

troubleshooting: softwareid, softwarename
softwareissue: softwareid, issueid, issuename

My index.php displays the softwarename from the troubleshooting table.
I want my issue.php page to display the issues associated with the softwareid which is passed through from the index page and still also display the software name from the troubleshooting table. I am hoping to create a navigation such as Home > Software Name > Issue Name...

Code: Select all

 
<?php
$con = mysql_connect("SERVER","USER","PW");
 
if (!$con)
  {
  die('Could not connect: ' . mysql_error());  
  }
mysql_select_db("DB", $con);
$result = mysql_query("SELECT troubleshooting.softwarename, softwareissue.issuename 
   FROM troubleshooting INNER JOIN softwareissue ON troubleshooting.softwareid =  softwareissue.issueid");
$softwarenamevalue = mysql_real_escape_string($_GET['softwarename']);
$softwareidvalue = mysql_real_escape_string($_GET['softwareid']);
$result = mysql_query("SELECT * FROM troubleshooting.softwarename WHERE troubleshooting.softwareid = $softwareidvalue");
$result = mysql_query("SELECT * FROM softwareissue WHERE softwareid = $softwareidvalue");
echo "<table border='0'><tr><td>" . $row = mysql_fetch_array($result) . "<br />";
while($row = mysql_fetch_array($result))
    {  
  echo "<tr>";
  echo "<td><a href = 'resolution.php?issueid=" . $row['issueid'] . "'>" . $row['issuename'] . 
"</a></td></tr>"; }
  
echo "</table>";
mysql_close($con);
?>