[Resolved] Querying from two variables?

mattpointblank

Hey guys,

I'm learning PHP (slowly) to replace phpNuke (yeah, I know) as the main system to operate my site. I'm currently writing a Reviews module.

Under some advice I've been changing the structure of my Reviews table so that I have a seperate table for Staff, who write the reviews. I've just added in a dropdown box on the reviews submission form, which looks up the $name values in the Staff table, and stores the selected staff member's $id in the Reviews table under 'author'. So far so good?

Now when I come to display the review itself, I have problems. Before, I just had php query the Reviews table, which contained the $Reviewer_Name value for each stored review. Now what I need to do, is basically tell php to:

Lookup the $author id stored in the Reviews Table, and display the relevant data associated with that $id on the page.

That on its own sounds simple, but the only way I can think of doing this is to select all the values from the Staff table, then display the relevant one. Is there a way to query it in such a way that it takes the $author id value stored in the review and then queries the Staff database just for that $id rather than everything? I'm just not sure how to write a query like that on the same page that already looks everything up for the review based on the URL (review.php?id=$value).

Sorry if this was a little wordy for a simple problem, I had to try to get my head around it as I wrote it..

dyntryx

Well, as far as I know you just need to execute two queries. I don't think there's a simple solution for doing this all with one query. As long as I understand correctly you want to display the article along with information on the author. But these are in two separate tables. So, what you would do is something like this:

<?php

// Connection.
mysql_connect('localhost', 'username', 'password');
mysql_select_db('database');

/* This will return author ids on any article matching the
    provided article id.
    If the author's id is the same throughout as it should be,
    then we should get no issues. */
$author_ids = mysql_query("SELECT author FROM articles WHERE id='$article_id'");

/* Just grab the first row and get the id, since all the
    rows should have the same id anyway. */
$row = mysql_fetch_row($author_ids);
$author_id = $row[0];

// This will return the author's information from the other database.
$author = mysql_query("SELECT * FROM staff WHERE id='$author_id'");

// Grab the only row that should have been returned.
$row = mysql_fetch_array($author);
// Assuming their name is in the "name" field...
$author = $row['name'];

echo "Written by $author...";

?>

Hope that helps. Let me know if this isn't what you're looking for.

mattpointblank

That worked great, after a little modification (I need to get better organised with my field names and such...), thanks a ton!

planetsim

You could make that just one query with a JOIN.

SELECT articles.author, staff.* 
FROM articles
LEFT JOIN staff ON (staff.id = articles.author)
WHERE id='$article_id'

mattpointblank

So basically that code is telling php that the author id is linked with the article? That might fix the next problem I had:

http://www.scenepointblank.com/matt/dev/reviews/review.php?page=U

So far I've managed to do everything I wanted (display the drop down box with the values of the author table, which displays their articles, and in the articles themselves, display the $author variable for that article). My problem now is filling the 'Reviewer' column on the above page. Would your method fix that?

edit: Also, I'm having trouble loading the $author value into the form when editing the articles.

thorpe

yeah. where possible you should avoid multiple queries. here is a small writeup, should explain how joins work in sql.

mattpointblank

Sorry to be a pain, but I'm not getting this. Here's what I'm doing:

<?php
include 'config.php';
include 'opendb.php';

$page = $_GET['page'];
$query = "SELECT Band_Name, Record_Name, id, Reviewer_Name, Record_Label FROM review_test 
WHERE Band_Name LIKE '$page%' ORDER BY Band_Name, Record_Name";
$query2 = "SELECT Staff_Name, id FROM staff ORDER BY Staff_Name";
$result = mysql_query($query) or die('Error : ' . mysql_error()); 
$result2 = mysql_query($query2) or die('Error : ' . mysql_error()); 

$query3 = "SELECT review_test.*, staff.* FROM review_test;
LEFT JOIN staff ON (staff.id = review_test.Reviewer_Name);
WHERE id='$id'";


//dropdown start
<!--dropdown code here-->
//end dropdown

echo "Reviews for $page<br><br>";

echo "</font>";

//this displays the review list
echo "<table width=\"95%\"  border=\"0\" cellspacing=\"0\" cellpadding=\"0\">";

echo "<tr><td width=\"55%\"><font size=\"2\" face=\"Verdana\"><strong>Record</strong></font></td>";
echo "<td width=\"25%\"><font size=\"2\" face=\"Verdana\"><strong>Label</strong></font></td>";
echo "<td width=\"10%\"><font size=\"2\" face=\"Verdana\"><strong>Reviewer</strong></font></td>";
echo "<td width=\"10%\" align=\"center\"><font size=\"2\" face=\"Verdana\"><strong>Admin</strong></font></td></tr>";
   while($row = mysql_fetch_array($result, MYSQL_NUM))
    {
        list($Band_Name, $Record_Name, $id, $Staff_Name, $Record_Label) = $row;
echo "<tr><td width=\"55%\"><font size=\"2\" face=\"Verdana\"><a href=\"reviews.php?id=$id\">$Band_Name - $Record_Name</a></font></td>";
echo "<td width=\"25%\"><font size=\"2\" face=\"Verdana\">$Record_Label</font></td>";
echo "<td width=\"10%\"><font size=\"2\" face=\"Verdana\">$Staff_Name</font></td>";
echo "<td width=\"10%\" align=\"center\"><font size=\"2\" face=\"Verdana\"><a href=\"reviewedit.php?id=$id\">edit</a> | <a href=\"reviewdel.php?id=$id\" onclick=\"return confirm('Are you sure you want to delete?')\">delete</a></font></td>";
echo "</tr>";
    }
echo "</table>";

echo "<font face=\"Verdana\" size=\"2\"><br><br><b><a href=\"index.php\">Write a review</a></b>.";
echo "</font>";

include 'closedb.php';
?>

All I've been able to get this page to do:
http://www.scenepointblank.com/matt/dev/reviews/review.php?page=U

Is either display the last alphabetic value for $author, for every article, or to do what it's currently doing, and display the value stored for $article_author in the article table.

mattpointblank

I ended up figuring this out myself with some research. I used a inner join like so:

$page = $_GET['page'];

$query = "SELECT reviews.id, Band_Name, Record_Name, Record_Label, Staff_Name

FROM reviews INNER JOIN staff ON Reviewer_Name = staff.Staff_id
WHERE Band_Name LIKE '$page%' ORDER BY Band_Name, Record_Name";