Listing IDs from a MySQL Query

[TMacFarlane]

Hi!

I am by no means a MySQL expert. I am only learning as I go along. I have gotten to the point where I have made a relational database: where the main table indexes repetitious data from other tables. I am trying to figure out a way to display several different results from one query. For the sake of my client's anonymity, let's think about this database as authors and quotes.

I would like to pop up a modal box that shows all the quotes of one author when clicking a link. So here are two tables in my database: one, "quotes", and one "authors". I have indexed the primary key in my authors to my quotes. Let's say we have five quotes from Shakespeare (ID=1 in "authors") and five quotes from Wilde (ID=2 in "authors"). I can easily create a separate query such as this:

$query_shakes ="SELECT quotes.quotes, quotes.auth_id, authors.auth_id, authors.authors WHERE quotes.auth_id = authors.auth_id AND authors.auth_id = 1";

... and so on.

The trouble is, I actually have 16 authors and almost a hundred quotes. It seems to be overkill to write a separate query for each author. Can't I simply use the query above without the "AND ..." part and then inject it later as a variable or something with the link?

Does what I am trying to do make sense? Can someone point me in the right direction?

[r937]

Does what I am trying to do make sense?

not yet

what does "inject it later" mean?

i thought you were looking to list the quotes of a specific author? you would definitely have to identify which one you want, so as not to return quotes for all authors

[TMacFarlane]

Yes-- here is what I want:

First, to make a single query that gets all quotes and authors. Then I want to refine the query so that a link (say, "Shakespeare") pops up a modal box that only shows the quotes from one single author (in this case, all Shakespeare's quotes.)

I started out writing the same query over and over again sixteen times and just appended "AND authors.auth_id = n"; (where n = the index of the specific author) to each one for the link. But that seemed too redundant. Surely that's not the way everybody does this sort of thing.

Couldn't I make the original query a variable ($query) and then do something like:

<?php echo $row_query['auth_id = 1']; ?> to display all the Shakespeare quotes?

[r937]

yeah, you could do that (except don't ask me for specifics, as i don't do php)

the query you want is --

SELECT authors.auth_id
     , authors.authors     
     , quotes.quotes
  FROM authors
INNER
  JOIN quotes
    ON quotes.auth_id = authors.auth_id

[TMacFarlane]

I already have that. That is not the problem.

I have essentially that query, with the following appended:

WHERE quotes.auth_id = n (where n is the index of the author)

... repeated sixteen times for each for each of the sixteen authors. That is working, but it just seems like too much unnecessarily repeated code. Why should I have to repeat that same query over and over again, just to get a single record? It seems mighty wasteful.

[guelphdad]

If you are going to list all of the authors why do you need the AND clause at all? And you certainly don't need to repeat your code 16 times. Just add an order by clause, order by author id or whatever and print out your data from there.

[TMacFarlane]

If you are going to list all of the authors why do you need the AND clause at all?

I don't if I use the inner join technique above. I would just append the WHERE clause in the 10:32 reply (sixteen times over.)

I considered using the ORDER BY instead, but that would mean that I need to keep track of the number of entries per author. What if another quote gets added (worse: what if someone else enters a quote without telling me!?), then I have to rewrite all my code, or the app gets broken.

[r937]

... repeated sixteen times for each for each of the sixteen authors.

i don't understand why you would do that

instead of 16 queries, just run 1, which returns all authors and their quotes to your application program

which then decides which of the 16 to "inject later" ...

... depending on the author id in the modal box, whatever that is

is some kind of AJAX application?

[guelphdad]

I don't if I use the inner join technique above. I would just append the WHERE clause in the 10:32 reply (sixteen times over.)

I considered using the ORDER BY instead, but that would mean that I need to keep track of the number of entries per author. What if another quote gets added (worse: what if someone else enters a quote without telling me!?), then I have to rewrite all my code, or the app gets broken.

Every time the page is visited the mysql query gets run again. So if new data is added then it would get shown the next time the page is loaded (or if you are using AJAX, immediately upon being inserted into the table).

Think of it this way, you have a bus with room for 16 people. With your way you come up to the bus stop, let one person on the bus, drive around the block and pick up the next person and drive around the block and pick up the next person.

We're proposing you stop the bus once, let everyone on and then drop them off at their destinations on the way. Is that clearer?

[TMacFarlane]

The bus analogy is an excellent example of what I am doing here! Let's pretend it is a bus returning from Atlantic City (something I love to do on the weekend. That way, the bus is as full of people as it ever will be--just like my initial MySQL query. However, there might be a few additional people who are not on the manifest.

So the first stop (Philadelphia,) it lets off 6 passengers. It could let off six--but the driver really shouldn't have to know. Next stop (Baltimore), it lets off 5, and then another 5 in DC.

So, you see, it is way more important for the driver to know what city to stop in than to know how many passengers are leaving at that city. Also, when the driver drops off the passengers in DC, he's not gonna go all the way back to AC to pick up everyone all over again.

This is exactly what I am pointing at. I want to do an initial query (pick up all the passengers.) Then I want to make a link (the city.) WHen someone clicks the link, I want a modal box showing me the names of all the people who got off there. I do not want to keep track of how many people are dropping off in the city, because that information could change. And I certainly do not want to pick up all the passengers all over again, just to drop off a few in that city.

Kapich?

[TMacFarlane]

Okay--

So, no, this isn't AJAX. It is just a straight MySQL query. I have set up the following:

mysql_select_db($database_authorConn, $authorConn);
$query_authorQuote = "SELECT quotes.quote_text
, quotes.quote_link
, quotes.author_id
, authors.author_name
, authors.author_id
FROM quotes
INNER JOIN authors
ON quotes.author_id = authors.author_id";
$authorQuote = mysql_query($query_authorQuote, $authorConn) or die(mysql_error());
$row_authorQuote = mysql_fetch_assoc($authorQuote);
$totalRows_authorQuote = mysql_num_rows($authorQuote);

Now, how to set up the link? I wrote this (mightily simplified):

<li><?php echo $row_authorQuote['author_name WHERE author_id = 1']; ?></li>

I got an empty list item. Can someone point out where I failed?

[guelphdad]

So you don't have a mysql problem, you have a php one. Ask a moderator to move the thread over to the PHP forum.

[r937]

Can someone point out where I failed?

somewhere in the php echo

what's the deal with the WHERE condition in there? i don't think that's valid

but this is the databases/mysql forum, not the php forum, and i don't do php

[TMacFarlane]

So you don't have a mysql problem, you have a php one. Ask a moderator to move the thread over to the PHP forum.

How do I do that?

[TMacFarlane]

Hi!

I am by no means a MySQL expert. I am only learning as I go along. I have gotten to the point where I have made a relational database: where the main table indexes repetitious data from other tables. I am trying to figure out a way to display several different results from one query. For the sake of my client's anonymity, let's think about this database as authors and quotes.

I would like to pop up a modal box that shows all the quotes of one author when clicking a link. So here are two tables in my database: one, "quotes", and one "authors". I have indexed the primary key in my authors to my quotes. Let's say we have five quotes from Shakespeare (ID=1 in "authors") and five quotes from Wilde (ID=2 in "authors"). I have the query that gets all the info, but I cannot script how to show all the Shakespeare quotes in one modal box, and all the Wilde quotes in another.

I tried this: <li><?php echo $row_query['auth_id = 1']; ?></li> but all I got was an empty list item.

Does what I am trying to do make sense? Can someone point me in the right direction?

[r937]

How do I do that?

click on one of the little red flags at the bottom left of each post

in this case, you should request that this thread be merged into the new thread you started in the php forum, so that people will have the benefit of the history to this probelm

[guido2004]

I guess the easiest way would be to load the query result set in an array:

$array = array();
while ($row = mysql_fetch_assoc($result)) {
  $array[$row['auth_id']]['author'] = $row['author'];
  $array[$row['auth_id']]['quotes'][$row['quote_id']] = $row['quote']
}
print_r($array);  // this line will display the array content so you can see how it is structured, you can delete it when you don't need it anymore 

Of course, you'll have to change this to suit your specific case. And then, you'll have write the part that will actually display the data on screen.

[TMacFarlane]

Thanks for this information--

I have some other things to attend to right now, but I will get back with the results as soon as I run them.

[TMacFarlane]

Waitaminit!

I can see the array, no problem. What I want to see is a small part of the array when I click a link. For example, when I click a link on William Shakespeare's link, I will see all the quotes from William Shakespeare in a modal box.

Should I assign a runtime variable somewhere? Should I "$_GET" that variable? What about a SET column--is it appropriate here? This is the part that is catching me up.