kingnutter

I have been looking into table JOINs and it appears my query below may be best sorted with a query containing a subquery. I am recalling and iterating through TEXT values into a form for the user to re-edit. At present I get the error "mysql_fetch_row(): supplied argument is not a valid MySQL result resource in..." With a bit of tinkering it has echoed just one of the moj_genre but without iterating through the whole set. Can anybody help? Here is the code: $query="SELECT moj_genre FROM genres WHERE genre_id = (SELECT genre_id FROM genrelinkcd WHERE moj_id='$id')"; $result=mysql_query($query); while ($row = mysql_fetch_row ($result)) { echo "$row". ', '; } And this is an example of the tables. Table: genres genre_id moj_genre 1 Rock 2 Pop 3 Jazz 4 Soul Table: genrelinkcd id genre_id moj_id 1 1 1 2 1 2 3 2 2 4 3 3

Sorry this is what I aiming at (but it still doesn't work): $query="SELECT moj_genre FROM genres WHERE genres.genre_id = genrelinkcd.genre_id WHERE genrelinkcd.moj_id=$id"; I want to select field 'moj_genre' from the table 'genres' where genre_id in the same table is the same as genre_id in table 'genrelinkcd', but only where 'moj_id' in that table is equal to $id. Phew.

Hi there, Can anyone tell me if I have the wrong syntax in this query. It doesn't seem to be working. If there is no error in the query I shall post further information. Thanks. KN $query="SELECT moj_genre FROM genres, genrelinkcd WHERE genres.genre_id = genrelinkcd.genre_id WHERE genrelinkcd.moj_id='$id'"; [code]

Can anybody tell me why the foreach loop below is only giving me two results when there should be more? <?php $query="SELECT genre_id FROM genrelinkcd WHERE moj_id=$id"; $result=mysql_query($query); $rows=mysql_fetch_array($result); foreach ($rows as $row) { echo "$row". ', '; } ?>" I'm always getting stuck on mysql_fetch_array, mysql_fetch_rows, etc despite having to rack my brains and Googling the answer for two days each time. Can anyone point me to a tutorial that spells it all in layman terms?

Excellent and simple explanation. Thank you very much.

Cheers. I'm not having much luck Googling this. How does a tag table work? (This is where I got stuck on a previous project) I am thinking that the field in my entry form should be the same as present (tags split by commas). I then split this into an array to enter into the tags table.If DISTINCT it creates a new entry but what happens to a duplicate tag? Is another instance created reffing a project_id? Or is there only one instance of each tag each having an expandable array of project_ids? Just a pointer to a good tutorial would be really helpful. Thanks.

Would a separate table for tags be the right move then?

Hi everyone, I am trying to create a tag cloud from all rows of field 'moj_genre' in table 'mojocd' which contains tags separated by commas, splitting them all up in one neat query. I have written the query below in hybrid MySql/English. Is this possible and, if so, what would be the correct syntax? "SELECT moj_genre FROM mojocd ***EXPLODE moj_genre on comma*** GROUP BY ***EXPLODEd results of moj_genre*** ORDER BY count DESC"

No worries. I've fixed it by changing the while loop to >=2000 and creating a blank image for that year. The JS code just isn't liking the last iteration for some reason.

OK. I've had a good read up about this. The only problem, if you look at my original code, is that I am generating the (year) image links using iteration of a decreasing string. How can I achieve this if CSS requires a definite image URL, or is there a way to incorporate PHP into it?

Fair comment.

The last one that works is 2002. 2001 functions as a link, but the rollover is inactive. If I reset while ($year >= 2001) to while ($year >= 2003) ...2004 is the last one to work properly. Weird, no?

Very interesting. I shall look into it further for further rollovers. Thanks for that. I'm still curious thought as to why the above code does not work for the final value of $year. Any thoughts?

I Googled CSS rollovers and couldn't find any solutions that didn't involve JS in one way or another. Any pointers to the best angle would be very welcome.

Hi there, I'm building rollover navigation by year using js and php. Everything is working fine except for the year 2001. I can link to the js file if you need to see it, but essentially it uses the file xxx_black.png as normal and finds xxx_red.png to use for the rolled over image. All images exist in the correct directory and have been created in the required colour. Any ideas? <?php $year = 2009; while ($year >= 2001) { ?> <td> <script> prepareImageSwap(document.body); </script> <a href="search_results.php?&yearsearch=<?php echo $year; ?>"> <img src="/images/<?php echo $year; ?>_black.png" alt="<?php echo $year; ?>"></a></td> <td><img src="/images/banner_colon.png" alt=":"></td> <?php $year--; } ?> </tr> </table>

All working brilliant. Excellent. Thank you all.

Thanks for the quick replies guys. Smerny, the current page that works has a url of: http://mojo.localhost/php-files/search_results.php

The first page of search results works perfectly in the code below, however further pages are blank. I think this is because the information is not being rePOSTed for each page but: a) I'm not quite sure how to do this. Is it just a hidden form with existing string values as defaults? and b) Is it better achieved with SESSIONs or some other solution? Thanks in advance, KN <form name="search" method="post" action="search_results.php"> Search for: <input type="text" name="find" /> in <Select NAME="field"> <Option VALUE="track_artist">Track Artist</option> <Option VALUE="track_title">Track Title</option> <Option VALUE="all">All</option> </Select> <input type="hidden" name="searching" value="yes" /> <input type="submit" name="search" value="Search" /> </form> <?php // includes include('conf.php'); include('functions.php'); $field = $_POST['field'] ; $find = $_POST['find'] ; $searching = $_POST['searching'] ; //This is only displayed if they have submitted the form if ($searching =="yes") { echo "<h2>Results</h2><p>"; //If they did not enter a search term we give them an error if ($find == "") { echo "<p>You forgot to enter a search term"; exit; } // Otherwise we connect to our Database // open database connection $connection = mysql_connect($host, $user, $pass) or die ('unable to connect!'); //select database mysql_select_db($db) or die ('unable to select database!'); $find = strtoupper($find); $find = strip_tags($find); $find = trim ($find); // Query database to find out how many rows in total result before pagination. N.B Could experiment with COUNT here. if ($field=='all') { $query = "SELECT t.moj_id, t.track_artist, t.track_title, m.moj_title FROM tracks t JOIN mojocd m ON t.moj_id = m.moj_id WHERE upper(t.track_artist) LIKE '%$find%' OR upper(t.track_title) LIKE '%$find%'"; $result = mysql_query($query, $connection); } else //Or we search for our single search term, in the field the user specified { $query = "SELECT track_artist, track_title FROM tracks WHERE upper($field) LIKE'%$find%'"; $result = mysql_query($query); } $result = mysql_query($query, $connection); $numrows = mysql_num_rows($result); // number of rows to show per page $rowsperpage = 10; // find out total pages $totalpages = ceil($numrows / $rowsperpage); // get the current page or set a default if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) { // cast var as int $currentpage = (int) $_GET['currentpage']; } else { // default page num $currentpage = 1; } // end if // if current page is greater than total pages... if ($currentpage > $totalpages) { // set current page to last page $currentpage = $totalpages; } // end if // if current page is less than first page... if ($currentpage < 1) { // set current page to first page $currentpage = 1; } // end if // the offset of the list, based on current page $offset = ($currentpage - 1) * $rowsperpage; if ($field=='all') { $query = "SELECT t.moj_id, t.track_artist, t.track_title, m.moj_title FROM tracks t JOIN mojocd m ON t.moj_id = m.moj_id WHERE upper(t.track_artist) LIKE '%$find%' OR upper(t.track_title) LIKE '%$find%' LIMIT $offset, $rowsperpage"; } else //Or we search for our single search term, in the field the user specified { $query = "SELECT track_artist, track_title FROM tracks WHERE upper($field) LIKE'%$find%' LIMIT $offset, $rowsperpage"; } $result = mysql_query($query); // while there are rows to be fetched... ?><table><?php while($list = mysql_fetch_assoc( $result )) { $counter++; $background_color = ( $counter % 2 == 0 ) ? ('white') : ('#E0E0E0'); echo '<tr><td style="background-color:'.$background_color.'">' ?> <b><?php echo $list['track_title']; ?></b> by <b><?php echo $list['track_artist']; ?></b> on <b><?php echo $list['moj_title']; ?></b></td></tr> <br> <?php } ?> </table> <?php //This counts the number or results - and if there wasn't any it gives them a little message explaining that $anymatches=mysql_num_rows($result); if ($anymatches == 0) { echo "Sorry, but we can not find an entry to match your query<br><br>"; } //And we remind them what they searched for echo "<b>Searched For:</b> " .$find; } /****** build the pagination links ******/ // range of num links to show $range = 3; // if not on page 1, don't show back links if ($currentpage > 1) { // show << link to go back to page 1 echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> "; // get previous page num $prevpage = $currentpage - 1; // show < link to go back to 1 page echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> "; } // end if // loop to show links to range of pages around current page for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) { // if it's a valid page number... if (($x > 0) && ($x <= $totalpages)) { // if we're on current page... if ($x == $currentpage) { // 'highlight' it but don't make a link echo " [<b>$x</b>] "; // if not current page... } else { // make it a link echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> "; } // end else } // end if } // end for // if not on last page, show forward and last page links if ($currentpage != $totalpages) { // get next page $nextpage = $currentpage + 1; // echo forward link for next page echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> "; // echo forward link for lastpage echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> "; } // end if /****** end build pagination links ******/ ?>

It looks like both SESSIONs or re-POSTs could would. Which is the more scalable solution, say when search results reach into the hundreds?

I'm pretty sure this is to do with JOIN, but I'll leave it to the experts to tell you properly. This is actually a MYSQL command so someone wil probably move this post to the right spot soon. Speaking from experience you understand.

I sorted this out with mysql_num_rows in the end and it works quite well. For the first page of results anyhow. Then the links to further search pages lead to nothing. I think it's because I should be rePOSTing my search terms. Or perhaps it's something to do with SESSIONS. Can anyone give me some pointers before I go off down the wrong path? <form name="search" method="post" action="search_results.php"> Search for: <input type="text" name="find" /> in <Select NAME="field"> <Option VALUE="track_artist">Track Artist</option> <Option VALUE="track_title">Track Title</option> <Option VALUE="all">All</option> </Select> <input type="hidden" name="searching" value="yes" /> <input type="submit" name="search" value="Search" /> </form> <?php // includes include('conf.php'); include('functions.php'); $field = $_POST['field'] ; $find = $_POST['find'] ; $searching = $_POST['searching'] ; //This is only displayed if they have submitted the form if ($searching =="yes") { echo "<h2>Results</h2><p>"; //If they did not enter a search term we give them an error if ($find == "") { echo "<p>You forgot to enter a search term"; exit; } // Otherwise we connect to our Database // open database connection $connection = mysql_connect($host, $user, $pass) or die ('unable to connect!'); //select database mysql_select_db($db) or die ('unable to select database!'); $find = strtoupper($find); $find = strip_tags($find); $find = trim ($find); // Query database to find out how many rows in total result before pagination. N.B Could experiment with COUNT here. if ($field=='all') { $query = "SELECT t.moj_id, t.track_artist, t.track_title, m.moj_title FROM tracks t JOIN mojocd m ON t.moj_id = m.moj_id WHERE upper(t.track_artist) LIKE '%$find%' OR upper(t.track_title) LIKE '%$find%'"; $result = mysql_query($query, $connection); } else //Or we search for our single search term, in the field the user specified { $query = "SELECT track_artist, track_title FROM tracks WHERE upper($field) LIKE'%$find%'"; $result = mysql_query($query); } $result = mysql_query($query, $connection); $numrows = mysql_num_rows($result); // number of rows to show per page $rowsperpage = 10; // find out total pages $totalpages = ceil($numrows / $rowsperpage); // get the current page or set a default if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) { // cast var as int $currentpage = (int) $_GET['currentpage']; } else { // default page num $currentpage = 1; } // end if // if current page is greater than total pages... if ($currentpage > $totalpages) { // set current page to last page $currentpage = $totalpages; } // end if // if current page is less than first page... if ($currentpage < 1) { // set current page to first page $currentpage = 1; } // end if // the offset of the list, based on current page $offset = ($currentpage - 1) * $rowsperpage; if ($field=='all') { $query = "SELECT t.moj_id, t.track_artist, t.track_title, m.moj_title FROM tracks t JOIN mojocd m ON t.moj_id = m.moj_id WHERE upper(t.track_artist) LIKE '%$find%' OR upper(t.track_title) LIKE '%$find%' LIMIT $offset, $rowsperpage"; } else //Or we search for our single search term, in the field the user specified { $query = "SELECT track_artist, track_title FROM tracks WHERE upper($field) LIKE'%$find%' LIMIT $offset, $rowsperpage"; } $result = mysql_query($query); // while there are rows to be fetched... ?><table><?php while($list = mysql_fetch_assoc( $result )) { $counter++; $background_color = ( $counter % 2 == 0 ) ? ('white') : ('#E0E0E0'); echo '<tr><td style="background-color:'.$background_color.'">' ?> <b><?php echo $list['track_title']; ?></b> by <b><?php echo $list['track_artist']; ?></b> on <b><?php echo $list['moj_title']; ?></b></td></tr> <br> <?php } ?> </table> <?php //This counts the number or results - and if there wasn't any it gives them a little message explaining that $anymatches=mysql_num_rows($result); if ($anymatches == 0) { echo "Sorry, but we can not find an entry to match your query<br><br>"; } //And we remind them what they searched for echo "<b>Searched For:</b> " .$find; } /****** build the pagination links ******/ // range of num links to show $range = 3; // if not on page 1, don't show back links if ($currentpage > 1) { // show << link to go back to page 1 echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> "; // get previous page num $prevpage = $currentpage - 1; // show < link to go back to 1 page echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> "; } // end if // loop to show links to range of pages around current page for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) { // if it's a valid page number... if (($x > 0) && ($x <= $totalpages)) { // if we're on current page... if ($x == $currentpage) { // 'highlight' it but don't make a link echo " [<b>$x</b>] "; // if not current page... } else { // make it a link echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> "; } // end else } // end if } // end for // if not on last page, show forward and last page links if ($currentpage != $totalpages) { // get next page $nextpage = $currentpage + 1; // echo forward link for next page echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> "; // echo forward link for lastpage echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> "; } // end if /****** end build pagination links ******/ ?>

Thanks. But I'm still confused about the placement. Should it be part of the sql query... $query = "COUNT (moj_id) WHERE SELECT t.moj_id, t.track_artist, t.track_title, m.moj_title FROM tracks t JOIN mojocd m ON t.moj_id = m.moj_id WHERE upper(t.track_artist) LIKE '%$find%' OR upper(t.track_title) LIKE '%$find%' LIMIT $offset, $rowsperpage"; ... which just doesn't look right or in this part of the code: $result = mysql_query($query); $r = mysql_fetch_row($result); $numrows = $r[0]; ...which doesn't seem right because is COUNT not an sql query only?

Hi everyone, I've been following the fantastic Pagination tutorial from this website, which makes everything very clear. Sadly when trying to customise for my own project I'm getting a bit mixed up. The results I wish to paginate are from a query of JOINed tables, and I'm not sure if I'm putting my COUNT in the right place. Perhaps I should have something like COUNT ($result). I would be grateful if someone has the time to have a quick scan and provide any pointers, also for potential future hiccups as I haven't proceeded past the errors as yet. Many thanks. KN <form name="search" method="post" action="search_results.php"> Search for: <input type="text" name="find" /> in <Select NAME="field"> <Option VALUE="track_artist">Track Artist</option> <Option VALUE="track_title">Track Title</option> <Option VALUE="all">All</option> </Select> <input type="hidden" name="searching" value="yes" /> <input type="submit" name="search" value="Search" /> </form> <?php // includes include('conf.php'); include('functions.php'); // Grab POST data sent from form $field = $_POST['field'] ; $find = $_POST['find'] ; $searching = $_POST['searching'] ; //This is only displayed if they have submitted the form if ($searching =="yes") { echo "<h2>Results</h2><p>"; //If they did not enter a search term we give them an error if ($find == "") { echo "<p>You forgot to enter a search term"; exit; } // Otherwise we connect to our Database // open database connection $connection = mysql_connect($host, $user, $pass) or die ('unable to connect!'); //select database mysql_select_db($db) or die ('unable to select database!'); $find = strtoupper($find); $find = strip_tags($find); $find = trim ($find); if ($field=='all') { $query = "SELECT COUNT t.moj_id, t.track_artist, t.track_title, m.moj_title FROM tracks t JOIN mojocd m ON t.moj_id = m.moj_id WHERE upper(t.track_artist) LIKE '%$find%' OR upper(t.track_title) LIKE '%$find%' LIMIT $offset, $rowsperpage"; } else //Or we search for our single search term, in the field the user specified { $query = "SELECT COUNT track_artist, track_title FROM tracks WHERE upper($field) LIKE'%$find%' LIMIT $offset, $rowsperpage"; } $result = mysql_query($query); $r = mysql_fetch_row($result); $numrows = $r[0]; // number of rows to show per page $rowsperpage = 10; // find out total pages $totalpages = ceil($numrows / $rowsperpage); // get the current page or set a default if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) { // cast var as int $currentpage = (int) $_GET['currentpage']; } else { // default page num $currentpage = 1; } // end if // if current page is greater than total pages... if ($currentpage > $totalpages) { // set current page to last page $currentpage = $totalpages; } // end if // if current page is less than first page... if ($currentpage < 1) { // set current page to first page $currentpage = 1; } // end if // the offset of the list, based on current page $offset = ($currentpage - 1) * $rowsperpage; if ($field=='all') { $query = "SELECT t.moj_id, t.track_artist, t.track_title, m.moj_title FROM tracks t JOIN mojocd m ON t.moj_id = m.moj_id WHERE upper(t.track_artist) LIKE '%$find%' OR upper(t.track_title) LIKE '%$find%' LIMIT $offset, $rowsperpage"; } else //Or we search for our single search term, in the field the user specified { $query = "SELECT track_artist, track_title FROM tracks WHERE upper($field) LIKE'%$find%' LIMIT $offset, $rowsperpage"; } $result = mysql_query($query); // while there are rows to be fetched... ?><table><?php while($list = mysql_fetch_assoc( $result )) { $counter++; $background_color = ( $counter % 2 == 0 ) ? ('white') : ('#E0E0E0'); echo '<tr><td style="background-color:'.$background_color.'">' ?> <b><?php echo $list['track_title']; ?></b> by <b><?php echo $list['track_artist']; ?></b> on <b><?php echo $list['moj_title']; ?></b></td></td> <br> <?php } ?> </table> <?php //This counts the number or results - and if there wasn't any it gives them a little message explaining that $anymatches=mysql_num_rows($result); if ($anymatches == 0) { echo "Sorry, but we can not find an entry to match your query<br><br>"; } //And we remind them what they searched for echo "<b>Searched For:</b> " .$find; } ?>

Ah. Very interesting. I shall do so and report back if I get jumbled. Thanks for the speedy reply.

Hi everyone, I have given this a good Googling but not found the answer. I am trying to query two tables to produce one result, hopefully in one seamless query. Below is a hybrid Plain English / PHP version of exactly what I am trying to achieve. { $data = mysql_query("SELECT moj_id, track_artist, track_title FROM tracks WHERE upper(track_artist) LIKE '%$find%' OR upper(track_title) LIKE '%$find%' THEN SELECT moj_title FROM mojocd WHERE moj_id IS THE SAME AS THE ONE IN THE FIRST PART OF THIS QUERY"); } Any pointers?