473,322 Members | 1,911 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Using the results of a query in another query

I'm trying to create a "genius" like search so that a user can enter a song name and it gives you a list of songs of the same genre that should "match". So far I have a search where it can return the song title and artist but what I need is for it to take the genre of the song (saved in a variable) and do a second search that will reurn a list of songs of the same genre. Here's the code i have so far...

Expand|Select|Wrap|Line Numbers
  1. // Build SQL Query  
  2. $query = "select * from songs where song_name  like \"%$trimmed%\"  
  3.   order by song_name";
  4.  
-----------------
Expand|Select|Wrap|Line Numbers
  1. // now you can display the results returned
  2.   while ($row= mysql_fetch_array($result)) {
  3.   $title = $row["song_name"];
  4.   $artist = $row["artist"];
  5.   $genre = $row["genre"];
  6.   $SongID = $row["SongID"];
  7.   $Buy = $row["purchase_link"];
  8.  
  9.   echo "$count.) $title" ;
  10.   echo " - " ;
  11.   echo  "$artist"  ;
  12.     $count++ ;
  13.   }
  14.  
Apr 6 '11 #1
10 1913
Rabbit
12,516 Expert Mod 8TB
Please use code tags. So we know what you want to do. You haven't said what problem you're having in implementing it.
Apr 6 '11 #2
Okay, that's done. Sorry, I'm new at this. basically, the code I've left is the query, the variables where the results are saved and the echo onto the screen. What I want to do is use what's in the $genre variable and use that in a second query that uses the genre of the song title searched (it's in the same row of the database) to give a list of songs of the same genre and to echo said songs onto the page of my website.
Apr 6 '11 #3
Oh, and the problem I am having in implementing it is that I don't know how to do it.
Apr 6 '11 #4
Rabbit
12,516 Expert Mod 8TB
Well, from the code, I can see that you already know how to build a query string. And you have access to the genre variable. So you should have no problem building a new query string using that variable and then executing it.

And from the code I can see that you know how to iterate through a recordset and output the values. So you just do the same thing for the new recordset.

So I don't see what trouble you're having. You just have to do what you did except with a different sql string.
Apr 6 '11 #5
Ah okay, so would I have to simply do another query where I replace song_name with genre and \"%$trimmed%\" with $genre ?
Apr 7 '11 #6
Rabbit
12,516 Expert Mod 8TB
That's pretty much it. Unless you want to return songs from all genres in the original recordset. In which case you just need to keep appending the genres to a comma-delimited string.
Apr 7 '11 #7
okay, so i have written
Expand|Select|Wrap|Line Numbers
  1. // Build SQL Query  
  2. $query = "select * from songs where song_name  like \"%$trimmed%\"  
  3.   order by song_name"; // EDIT HERE and specify your table and field names for the SQL query
  4.  
  5. $query = "select * from songs where genre  like \"%$genre%\"  
  6.   order by song_name";
  7.  
but the variable $genre isn't saved until later. how would i save it before hand as a variable so taht it can be used in the search?
Apr 8 '11 #8
Rabbit
12,516 Expert Mod 8TB
If it's not saved till later, then run it after it's saved.
Apr 8 '11 #9
okay, so I have put the query in after it is saved:

Expand|Select|Wrap|Line Numbers
  1. <?php
  2.  
  3.   // Get the search variable from URL
  4.   $var = @$_GET['q'] ;
  5.   $trimmed = trim($var); //trim whitespace from the stored variable
  6.  
  7. // rows to return
  8. $limit=10; 
  9.  
  10. // check for an empty string and display a message.
  11. if ($trimmed == "")
  12.   {
  13.   echo "<p>Please enter a search...</p>";
  14.   exit;
  15.   }
  16.  
  17. // check for a search parameter
  18. if (!isset($var))
  19.   {
  20.   echo "<p>We dont seem to have a search parameter!</p>";
  21.   exit;
  22.   }
  23.  
  24. //connect to your database ** EDIT REQUIRED HERE **
  25. mysql_connect("localhost","w04dgaffey","melon"); //(host, username, password)
  26.  
  27. //specify database ** EDIT REQUIRED HERE **
  28. mysql_select_db("w04dgaffey") or die("Unable to select database"); //select which database we're using
  29.  
  30. // Build SQL Query  
  31. $query = "select * from songs where song_name  like \"%$trimmed%\"  
  32.   order by song_name"; // EDIT HERE and specify your table and field names for the SQL query
  33.  
  34.  
  35.  
  36.  
  37.  
  38.  $numresults=mysql_query($query);
  39.  $numrows=mysql_num_rows($numresults);
  40.  
  41. // If we have no results, offer a google search as an alternative
  42.  
  43. if ($numrows == 0)
  44.   {
  45.   echo "<h4>Results</h4>";
  46.   echo "<p>Sorry, your search: &quot;" . $trimmed . "&quot; returned zero results</p>";
  47.  
  48. // google
  49.  echo "<p><a href=\"http://www.google.com/search?q=" 
  50.   . $trimmed . "\" target=\"_blank\" title=\"Look up 
  51.   " . $trimmed . " on Google\">Click here</a> to try the 
  52.   search on google</p>";
  53.   }
  54.  
  55. // next determine if s has been passed to script, if not use 0
  56.   if (empty($s)) {
  57.   $s=0;
  58.   }
  59.  
  60. // get results
  61.   $query .= " limit $s,$limit";
  62.   $result = mysql_query($query) or die("Couldn't execute query");
  63.  
  64. // display what the person searched for
  65. echo "<p>You searched for: &quot;" . $var . "&quot;</p>";
  66.  
  67. // begin to show results set
  68. echo "Results";
  69. $count = 1 + $s ;
  70.  
  71. // now you can display the results returned
  72.   while ($row= mysql_fetch_array($result)) {
  73.   $title = $row["song_name"];
  74.   $artist = $row["artist"];
  75.   $genre = $row["genre"];  
  76.   $SongID = $row["SongID"];
  77.   $Buy = $row["purchase_link"];
  78.  
  79.   $query = "select * from songs where genre  like $genre  
  80.   order by song_name";
  81.  
  82.   echo "$count.)&nbsp;$title - " ;
  83.   echo  "$artist - "  ;
  84.   printf('<a href="%s">Buy this track</a>', $Buy);
  85.   $count++ ;
  86.   }
  87.  
  88. $currPage = (($s/$limit) + 1);
  89.  
  90.  
  91. //break before paging
  92.   echo "<br />";
  93.  
  94.   // next we need to do the links to other results
  95.   if ($s>=1) { // bypass PREV link if s is 0
  96.   $prevs=($s-$limit);
  97.   print "&nbsp;<a href=\"$PHP_SELF?s=$prevs&q=$var\">&lt;&lt; 
  98.   Prev 10</a>&nbsp&nbsp;";
  99.   }
  100.  
  101. // calculate number of pages needing links
  102.   $pages=intval($numrows/$limit);
  103.  
  104. // $pages now contains int of pages needed unless there is a remainder from division
  105.  
  106.   if ($numrows%$limit) {
  107.   // has remainder so add one page
  108.   $pages++;
  109.   }
  110.  
  111. // check to see if last page
  112.   if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {
  113.  
  114.   // not last page so give NEXT link
  115.   $news=$s+$limit;
  116.  
  117.   echo "&nbsp;<a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 &gt;&gt;</a>";
  118.   }
  119.  
  120. $a = $s + ($limit) ;
  121.   if ($a > $numrows) { $a = $numrows ; }
  122.   $b = $s + 1 ;
  123.   echo "<p>Showing results $b to $a of $numrows</p>";
  124.  
  125. ?>
  126.  
but it isn't running the query or isn't displaying the results of said query.
Apr 12 '11 #10
Rabbit
12,516 Expert Mod 8TB
You built the sql string but you never execute it and you don't output the results.
Apr 12 '11 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Frank | last post by:
Can I store this PHP code in another page, then refernece it on multiple pages using Require or Include ??? //results from sql query for ($i=0; $i<$number; $i++) { $ID =...
1
by: Frank Maestas | last post by:
Hello, I have been trying to figure this out but no luck. Lets say I have a query that searches for people living in Colorado. That results in a list on a "results.php" page. Now I want to...
8
by: san | last post by:
Hi, I wanted to know if this is possible and if so, how do I do it. Say, I have a query "SELECT * FROM Table WHERE Column="some_value". This executes on a very large data set and I would like...
2
by: Stan | last post by:
Hello all, I'm looking for some hints as to how to use the results of a query in another query. I assume I'm 'thinking' wrong in how to solve this, so I'm hoping someone can clobber me and send...
1
by: Alex Hemingway | last post by:
I have a table Products consisting of the following fields :- ProductID, ProductName, PriceLevel, UnitPrice and a query which returns all products with a specific PriceLevel. What I would like...
3
by: Richard Coutts | last post by:
I have a query created by the "Find Unmatched" query that lists items in a table that were not selected by another query. The Query does what it needs to do, but I can't edit any of the resulting...
6
by: lakshmi | last post by:
Hi all I'm trying to traverse through the results from a query that returns more than 1 row. The data reader reads only the first row. The following code doesn't work. Let me know what's wrong....
1
by: bgreenspan | last post by:
Hi Everyone, I'm back for some more expert help. Here's what I am doing and what I tried. My database has entries with Contract Names and Expiry Dates, among other fields. I have a form...
4
by: meendar | last post by:
Hi to all, I just need to get two fields from a table and manipulate the results in next query of a procedure.I planned to code like what you see below, create procedure marks1 as @ sql1 as...
3
by: zahmbonnie | last post by:
I have created two different queries and I would like take the results from one and subtract the results from the other. I can not figure out how to write the saved query name in my new query. Saved...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.