Problem with code

cretam

I am trying to display a result from a query but it comes up with an error "Could not locate the specified pub ID".

<?php

include("Connections/conn.php");

// Get the publication name from the database
$id = $_GET['id'];
$pub = @mysql_query(
    "SELECT id, pubname, pubdesc, pubno, pubdate, authorid, formatid, orgid, pages, cost FROM pub WHERE id='$id'");
if (!$pub) {
  exit('Unable to load the publication from the database.');
}
if (mysql_num_rows($pub) < 1) {
  exit('Could not locate the specified pub ID.');
}

$pub = mysql_fetch_array($pub);
$pubname = $pub['pubname'];
$pubdesc = $pub['pubdesc'];
$pubdate = $pub['pubdate'];
$cost = $pub['cost'];
$pubno = $pub['pubno'];
$pages = $pub['pages'];
$aid = $pub['authorid'];
$fid = $pub['formatid'];
$oid = $pub['orgid'];
//Filter out html code
$pubname = htmlspecialchars($pubname);
$aname = @mysql_query(
    "SELECT * FROM author WHERE id='$aid'");
while ($row = mysql_fetch_array($aname)) {
echo "<h3>Search Results - Environmental Publications Database</h3>";
echo "<p><b>Title: $pubname</b><br><b>Author:</b> $row[name]<br><b>Description: </b>$pubdesc<br>";
}
$fname = @mysql_query(
    "SELECT * FROM format WHERE id='$fid'");
while ($row = mysql_fetch_array($fname)) {
echo "<b>Format:</b> $row[name]<br><b>Date:</b>$pubdate<br>";
}
$oname = @mysql_query(
    "SELECT * FROM org WHERE id='$oid'");
while ($row = mysql_fetch_array($oname)) {
echo "<b>Available from:</b> $row[name]<br><b>Cost:</b>$cost<br><b>Publication Number:</b>$pubno<br><b>Pages:</b>$pages<br>";
}
$cname = @mysql_query(
    "SELECT * FROM category WHERE id='$cid'");
while ($row = mysql_fetch_array($cname)) {
echo "<b>Sector:</b> $row[name]<br>";
}

?>

Piranha

It seems like you don't have a pub with that id. Are you sure that it really do exist?

cretam

Yes it really does exist.

cretam

This is what I am trying to do. I want to select a letter of the alphabet to display the authors which link to the publications they've written which then links to the publication details ie name, author, available from, date, format, etc.

It all appears to be working ok except it won't display the details page
Code for the page with the alphabet links

<?php 

include("Connections/conn.php");
?>

<body>
<h3>Environmental Publications Database - Advanced Search</h3>
<p><strong><u>Filter by:</u> </strong></p>
 <label><strong>Author: </strong></label>
        <strong>
        <label></label>
        </strong>
        <label>
		<?php 
	// Output letters as links: (Rem. ascii range from 65 to 90 are letters A to Z!)
for($x=65;$x<=90;$x++)
{
if ($x==ord($subsel)) echo "<b>".chr($x)."</b>";
else
echo "<a href=\"publicationlist.php?letter=".chr($x)."\">".chr($x)."</a> ";
}
$letter = $_GET["letter"]; 
?>



    <br />
    </label>
    <p><strong>Availability:</strong>
	<?php 
// Output letters as links: (Rem. ascii range from 65 to 90 are letters A to Z!)
for($x=65;$x<=90;$x++)
{
if ($x==ord($subsel)) echo "<b>".chr($x)."</b>";
else
echo "<a href=\"publicationlist.php?by=".chr($x)."\">".chr($x)."</a> ";
}
$by = $_GET["by"]; 
?>

	</p>
<p>
      <label><strong>Sector:</strong></label>
      <?php 
	 $cats = @mysql_query('SELECT id, name FROM category order by name asc');
if (!$cats) {
  exit('<p>Error retrieving categories from database!<br />' .
      'Error: ' . mysql_error() . '</p>');
}



// how many columns we want our table to have
$columns = 4;



$i = 0;
echo "<table>\n"; 

while ($cat = mysql_fetch_array($cats)) {
  $id = $cat['id'];
  $name = htmlspecialchars($cat['name']);
//while ($row = mysql_fetch_assoc($result)) {

if (($i % $columns) == 0) {
   echo " <tr>\n";
}

echo "  <td><a href='publicationlist.php?cat=$id'>" . $cat['name'] . "</a></td>\n";

if (($i % $columns) == ($columns - 1)) {
    echo " </tr>\n";
}

$i++;
}

// find out how many empty <td></td> we need to output now
// to make the html correct and well formed
$remainder = $columns - ($i % $columns);

// do we have remainders?
if ($remainder > 0) {
    echo str_repeat("  <td></td>\n", $remainder);
    echo " </tr>\n";
} 
?>

Code for the list of authors page

<?php 

include("Connections/conn.php");



if (isset($_GET['by'])) {
    $sort = $_GET['by'];
    $query = "SELECT id, name FROM org WHERE name LIKE '$sort%' ORDER BY name";
	$result = mysql_query($query) or die ("$Query failed: ".mysql_error());
	/*Printing results in HTML*/
	print "<table>\n";
	while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {


echo "<a href=\"org.php?by=".$line['id']."\">".$line['name']."</a> </br> ";
}

}

if (isset($_GET['letter'])) {
$sort = $_GET['letter'];
$query = "SELECT id, name FROM author WHERE name LIKE '$sort%' ORDER BY name";
$result = mysql_query($query) or die ("$Query failed: ".mysql_error());
/*Printing results in HTML*/
print "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {


echo "<a href=\"author.php?by=".$line['id']."\">".$line['name']."</a> </br> ";
}

}

if (isset($_GET['cat'])) {
$sort = $_GET['cat'];
$query = "SELECT pubid FROM pubcategory WHERE categoryid = $sort";
$result = mysql_query($query) or die ("$Query failed: ".mysql_error());

}


?>

Code for the list of publications page

<?php 

include("Connections/conn.php");


if (isset($_GET['letter'])) {
    $sort = $_GET['letter'];
    $query = "SELECT id, pubname, authorid FROM pub WHERE authorid LIKE '$sort%' ORDER BY pubname";
	$result = mysql_query($query) or die ("$Query failed: ".mysql_error());
	/*Printing results in HTML*/
	print "<table>\n";
	while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {


echo "<a href=\"author_pub.php?by=".$line['id']."\">".$line['pubname']."</a> </br> ";
}

}



?>

Code for the publication details page

<?php

include("Connections/conn.php");

// Get the publication name from the database
$id = $_GET['id'];
$pub = @mysql_query(
    "SELECT id, pubname, pubdesc, pubno, pubdate, authorid, formatid, orgid, pages, cost FROM pub WHERE id='$id'");
if (!$pub) {
  exit('Unable to load the publication from the database.');
}

$pub = mysql_fetch_array($pub);
$pubname = $pub['pubname'];
$pubdesc = $pub['pubdesc'];
$pubdate = $pub['pubdate'];
$cost = $pub['cost'];
$pubno = $pub['pubno'];
$pages = $pub['pages'];
$aid = $pub['authorid'];
$fid = $pub['formatid'];
$oid = $pub['orgid'];
//Filter out html code
$pubname = htmlspecialchars($pubname);
$aname = @mysql_query(
    "SELECT * FROM author WHERE id='$aid'");
while ($row = mysql_fetch_array($aname)) {
echo "<h3>Search Results - Environmental Publications Database</h3>";
echo "<p><b>Title: $pubname</b><br><b>Author:</b> $row[name]<br><b>Description: </b>$pubdesc<br>";
}
$fname = @mysql_query(
    "SELECT * FROM format WHERE id='$fid'");
while ($row = mysql_fetch_array($fname)) {
echo "<b>Format:</b> $row[name]<br><b>Date:</b>$pubdate<br>";
}
$oname = @mysql_query(
    "SELECT * FROM org WHERE id='$oid'");
while ($row = mysql_fetch_array($oname)) {
echo "<b>Available from:</b> $row[name]<br><b>Cost:</b>$cost<br><b>Publication Number:</b>$pubno<br><b>Pages:</b>$pages<br>";
}
$cname = @mysql_query(
    "SELECT * FROM category WHERE id='$cid'");
while ($row = mysql_fetch_array($cname)) {
echo "<b>Sector:</b> $row[name]<br>";
}

?>

Crowly

Havent read the whole thread, just the start of the first post and i noticed this error in your script, you need to add ". and ." around $id. The error you do is telling the select to look for the row where id is the TEXT "$id" and not the value of the variable $id.
Change this:

$pub = @mysql_query( 
    "SELECT id, pubname, pubdesc, pubno, pubdate, authorid, formatid, orgid, pages, cost FROM pub WHERE id='$id'");

$pub = @mysql_query( 
    "SELECT id, pubname, pubdesc, pubno, pubdate, authorid, formatid, orgid, pages, cost FROM pub WHERE id='".$id."'");
#change these also
$aname = @mysql_query( 
    "SELECT * FROM author WHERE id='".$aid."'"); 
while ($row = mysql_fetch_array($aname)) { 
echo "<h3>Search Results - Environmental Publications Database</h3>"; 
echo "<p><b>Title: $pubname</b><br><b>Author:</b> $row[name]<br><b>Description: </b>$pubdesc<br>"; 
} 
$fname = @mysql_query( 
    "SELECT * FROM format WHERE id='".$fid."'"); 
while ($row = mysql_fetch_array($fname)) { 
echo "<b>Format:</b> $row[name]<br><b>Date:</b>$pubdate<br>"; 
} 
$oname = @mysql_query( 
    "SELECT * FROM org WHERE id='".$oid."'"); 
while ($row = mysql_fetch_array($oname)) { 
echo "<b>Available from:</b> $row[name]<br><b>Cost:</b>$cost<br><b>Publication Number:</b>$pubno<br><b>Pages:</b>$pages<br>"; 
} 
$cname = @mysql_query( 
    "SELECT * FROM category WHERE id='".$cid."'"); 
while ($row = mysql_fetch_array($cname)) { 
echo "<b>Sector:</b> $row[name]<br>"; 
}

Or you can use sprintf like this (replace %d with %s if you want text instead of a number):

$query=sprintf("SELECT id, pubname, pubdesc, pubno, pubdate, authorid, formatid, orgid, pages, cost FROM pub WHERE id='%d'",$id);
$pub = @mysql_query($query);

The good thing about storing the query in its own variable is that you can echo it afterwards to debugging. Ex

$id=1;

$query=sprintf("SELECT id, pubname, pubdesc, pubno, pubdate, authorid, formatid, orgid, pages, cost FROM pub WHERE id='%d'",$id);
echo $query;
#prints SELECT id, pubname, pubdesc, pubno, pubdate, authorid, formatid, orgid, pages, cost FROM pub WHERE id='1'

$query="SELECT id, pubname, pubdesc, pubno, pubdate, authorid, formatid, orgid, pages, cost FROM pub WHERE id='$id'";
echo $query;
#prints SELECT id, pubname, pubdesc, pubno, pubdate, authorid, formatid, orgid, pages, cost FROM pub WHERE id='$id'

$query="SELECT id, pubname, pubdesc, pubno, pubdate, authorid, formatid, orgid, pages, cost FROM pub WHERE id='".$id."'";
echo $query;
#prints SELECT id, pubname, pubdesc, pubno, pubdate, authorid, formatid, orgid, pages, cost FROM pub WHERE id='1'

cretam

Hi Crowly,
I did as you suggested but it didn't make any difference. After I left it alone for awhile and came back I found I had the variable as 'id' and not 'by' (first line of code in the first block).
$id = $_GET['by'];
I will remember the sprintf though - thanks.
Thanks for your help.

cretam

I am having problems with displaying the publications when a category is selected. I can get it to show the id number but not the name from the publications table.
The category table is a joining table. ie fields pubid and categoryid.

Here is the code I am having trouble with

[code=php]if (isset($_GET['cat'])) {
$sort = $_GET['cat'];
$query = "SELECT pubid, categoryid FROM pubcategory WHERE categoryid LIKE $sort ";
$result = mysql_query($query) or die ("$Query failed: ".mysql_error());

/Printing results in HTML/
print "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {

echo "<a href=\"cat_pub.php?by=".$line['id']."\">".$line['pubid']."</a> </br> ";
}

}[/code]
Where it echos the pubid, I want it to echo the pubname which is a field in the publications table.

cretam

Three more questions, how easy is it to add pagination to this code. I want it to only display 10 records to a page with a previous and back buttons.
Also I want it to display a message when no records are found "No records found"
Also when there are records, I want it to display "Select one from the list below"