Retrieve data from DB?

move3rd

Hi,

I'm trying to retrieve and display data from a DB.

<? // start the session 
session_start(); 
header("Cache-control: private"); //IE 6 Fix 

// Asign session variables
$cd_title = $_POST['cd_title']; 
$artist = $_POST['artist']; 
?>
<table width="100%" border="0" cellpadding="5">
  <tr> 
    <td width="41%">Welcome <? echo $_SESSION['username']; ?></td>
    <td width="59%"><div align="right"></div></td>
  </tr>
  <tr> 
    <td height="29"><a href="index.php">Home</a> : <a href="logout.php"> Log-out</a></td>
    <td><div align="right"></div></td>
  </tr>
</table><table width="100%" border="0" cellpadding="5">
  <tr>
    <td>
<?
echo "Edit Tracks On $cd_title";
echo " by $artist";


?></td>
  </tr>
</table>


<table width='400' border="1" cellpadding='5' cellspacing='1' bordercolor="#FFFFFF">
  <tr> 
    <td bordercolor="#CCCCCC" bgcolor='#E2E2E2'>Track</td>
    <td>&nbsp;</td>
  </tr>
  <tr> 
  <tr>
    <td width='75%' bordercolor="#CCCCCC" bgcolor='#F4F4F4'><table width="100%">
        <tr> 

<? 

include "db.php";

// Adslashes to cencel out possible input of " " ''   

addslashes($artist);

$sql = "SELECT * FROM `cds` WHERE `artist` = '$artist'";

$result = mysql_query($sql);

if (!$result) {
   echo "Could not successfully run query ($sql) from DB: " . mysql_error();
   exit;
              }

if (mysql_num_rows($result) == 0) {
	echo "<h4 align=center>Could not find any track's by ".$artist."!</h4>";  

   exit;
                                  }

while ($row = mysql_fetch_array($result)) {
extract($row);
echo "<td>".$track_num."</td>";

 echo "<td>".$track_title."</td>";
                                           }

mysql_free_result($result);
mysql_close($conn); ?>

    </tr>
  </table> </td>
<td width='25%' height="17" bordercolor="#CCCCCC" bgcolor='#F4F4F4'> </td>
  </tr>
</table>

Its not outputting anthing from the db. I know the $artist variable is set correctly becouse I echo'd it at the top of the page.

dalecosp

If you are sure that the query is valid and should return data, try rewriting your echo statement without using [man]extract/man:

while ($row = mysql_fetch_array($result)) {

echo "<td>".$row['track_num']."</td>";

 echo "<td>".$row['track_title']."</td>";
                                           }

HTH,

move3rd

I gave that ago but it didnt work so I replaced the variable $artist with a value (Jo)that I know is in the db colum artist.

It gives this error:
Could not successfully run query (SELECT * FROM cds WHERE artist = Jo) from DB: Unknown column 'Jo' in 'where clause'

laserlight

Use:
SELECT * FROM cds WHERE artist = 'Jo'

The reason is that "Jo" is interpreted as another column name.
Since the column Jo does not exist, the query does not work.

move3rd

Thanks laserlight I wasnt sure wether you only needed '$variables' in bracks and not actual values.

I did as you suggested and error disapeared but it still isnt returning anthing from the DB.

laserlight

Post the new code snippet.

move3rd

<? 	  

include "db.php";

// Adslashes to cencel out possible input of " " ''   

addslashes($artist);

$sql = "SELECT * FROM `cds` WHERE `artist` = 'Jo'";

$result = mysql_query($sql);

if (!$result) {
   echo "Could not successfully run query ($sql) from DB: " . mysql_error();
   exit;
              }

if (mysql_num_rows($result) == 0) {
	echo "<h4 align=center>Could not find any track's by ".$artist."!</h4>";  

   exit;
                                  }

while ($row = mysql_fetch_array($result)) {

echo "<td>".$row['track_num']."</td>"; 

echo "<td>".$row['track_title']."</td>"; 
                                           }

mysql_free_result($result);
mysql_close($conn); 
?>

laserlight

Here's an example of a test version of the script:

<?php
error_reporting(E_ALL); //for debugging
include "db.php";

$artist = 'Jo'; //set for testing

$sql = "SELECT `track_num`, `track_title`
	FROM `cds`
	WHERE `artist`='{$artist}'";
$result = mysql_query($sql)
	OR die(mysql_error()); //for debugging

if (mysql_num_rows($result) == 0) {
	die("Could not find any tracks by '{$artist}'!");
}

while ($row = mysql_fetch_array($result)) {
	echo "<td>" . $row['track_num'] . "</td>";
	echo "<td>" . $row['track_title'] . "</td>";
}
?>

move3rd

I feel like such an idiot. 🙁

I recently split a DB table (cds) into two tables (cds & tracks) to make things abit neeter. I forgot to change the table to tracks in the query.

Valuable lesson learned! I need to think very carfully before I change anthing!

Thanks for your help

dalecosp

Originally posted by move3rd
I feel like such an idiot. 🙁

No need for that ....

Valuable lesson learned! I need to think very carfully before I change anthing!

Just one?

I see 3 lessons here (kinda appropriate, given your username 😃 ):

extract() doesn't work on non-associative arrays by default, which was the first issue that led to the $row['value'] change. See the manpage for more information ...
MySQL expects strings to be literal quoted;

and, lastly:

Keep track of your table names! 😉

We were glad to help ... Keep on PHPing!