solgun Posted July 1, 2008 Share Posted July 1, 2008 Hi all, im building this engine that gets Categories and images from database. Categories are independent, and images are linked to categories on the database i have this table for Categories: ID Category Subcategory Type And this table for images ID Foto Category How can i from a single query get First the Categories and then the images to after fetch_array() and do while() to echo all the results? i've tryied this but it doubles me the results and doesn't give me the images: $sql_results = mysql_query("SELECT A.ID, A.Category, A.Sub, A.Tipo, B.ID FROM categorias as A, fotos as B WHERE A.Tipo='Imagem'"); Please help ??? Thanks Link to comment https://forums.phpfreaks.com/topic/112702-search-engine-problem/ Share on other sites More sharing options...
br0ken Posted July 1, 2008 Share Posted July 1, 2008 You need to link the two tables together in your query <?php $sql_results = mysql_query("SELECT A.ID, A.Category, A.Sub, A.Tipo, B.ID FROM categorias as A, fotos as B WHERE A.Tipo='Imagem' AND A.ID = B.Category"); ?> Link to comment https://forums.phpfreaks.com/topic/112702-search-engine-problem/#findComment-578773 Share on other sites More sharing options...
solgun Posted July 1, 2008 Author Share Posted July 1, 2008 First of all thanks for the reply, that gives me the images with categories, and there is also images with not categories (NULL field)... Anyways thats not the thing i want.. I want the Categories to be sorted and then the images.. like: Category Air Category Water Category Fire Image with no category 1.jpg Image with no category 2.jpg Link to comment https://forums.phpfreaks.com/topic/112702-search-engine-problem/#findComment-578799 Share on other sites More sharing options...
solgun Posted July 1, 2008 Author Share Posted July 1, 2008 $sql_results = mysql_query("SELECT A.ID, A.Categoria, A.Sub, A.Tipo, B.ID FROM categorias as A, fotos as B WHERE A.Tipo='Imagem' AND A.ID=B.Categoria"); while(list($id, $categoria, $sub, $tipo, $imgid) = mysql_fetch_row($sql_results)) { if ($imgid != '') { echo 'ID: '.$imgid .'(IMG)<br/>'; } else { echo 'ID: '.$id.'(Category)<br/>'; } } I hope this can make things more clear of what im asking Link to comment https://forums.phpfreaks.com/topic/112702-search-engine-problem/#findComment-578829 Share on other sites More sharing options...
solgun Posted July 1, 2008 Author Share Posted July 1, 2008 if you don't understand please ask me to be more expecific Link to comment https://forums.phpfreaks.com/topic/112702-search-engine-problem/#findComment-579301 Share on other sites More sharing options...
lemmin Posted July 1, 2008 Share Posted July 1, 2008 I think this should do what you want: "SELECT A.ID, A.Categoria, A.Sub, A.Top, B.ID FROM categorias as A LEFT JOIN fotos as B ON A.ID = B.Categoria" Link to comment https://forums.phpfreaks.com/topic/112702-search-engine-problem/#findComment-579310 Share on other sites More sharing options...
solgun Posted July 1, 2008 Author Share Posted July 1, 2008 we are almost there! that gives me all the categories and images inside each category.. but the images i want to be show are the ones of the category being viewed... if im not viewing any category it shows the images in the root... Thanks for the tip Link to comment https://forums.phpfreaks.com/topic/112702-search-engine-problem/#findComment-579372 Share on other sites More sharing options...
lemmin Posted July 1, 2008 Share Posted July 1, 2008 I was just trying to show the JOIN. You probably want that other criteria in there: "SELECT A.ID, A.Categoria, A.Sub, A.Top, B.ID FROM categorias as A LEFT JOIN fotos as B ON A.ID = B.Categoria WHERE A.Tipo='Imagem'" I think that is what you mean, I'm not sure though. Link to comment https://forums.phpfreaks.com/topic/112702-search-engine-problem/#findComment-579375 Share on other sites More sharing options...
solgun Posted July 1, 2008 Author Share Posted July 1, 2008 i don't get the left join, the query is still not giving me the images where Category is '' or '0'... Link to comment https://forums.phpfreaks.com/topic/112702-search-engine-problem/#findComment-579390 Share on other sites More sharing options...
lemmin Posted July 1, 2008 Share Posted July 1, 2008 Oh, sorry, I read that backwords. I thought you wanted only categories that had images. If you change it to a RIGHT JOIN it should be what you want. Link to comment https://forums.phpfreaks.com/topic/112702-search-engine-problem/#findComment-579458 Share on other sites More sharing options...
solgun Posted July 1, 2008 Author Share Posted July 1, 2008 $sql_results = mysql_query("SELECT A.ID, A.Categoria, A.Sub, A.Tipo, B.ID FROM categorias as A RIGHT JOIN fotos as B ON A.ID = B.Categoria"); while(list($id, $categoria, $sub, $tipo, $imgid) = mysql_fetch_row($sql_results)) { if ($imgid != '') { echo 'ID: '.$imgid .'(IMG)<br/>'; } else { echo 'ID: '.$id.'(Category)<br/>'; } } ~This is what i get: ID: 1(IMG) ID: 2(IMG) ID: 3(IMG) ID: 4(IMG) I want the result to be: Viewing root ID: 1(Category) ID: 2(Category) ID: 3(Category) ID: 1(IMG) ID: 2(IMG) ID: 3(IMG) ID: 4(IMG) viewing category 2 ID: 4(Category) ID: 6(Category) ID: 9(IMG) ID: 11(IMG) ID: 12(IMG) ID: 13(IMG) So like at the start all categories and images are shown independent... but if i select a category it shows the categories inside that category and the images inside the selected category. Link to comment https://forums.phpfreaks.com/topic/112702-search-engine-problem/#findComment-579501 Share on other sites More sharing options...
lemmin Posted July 1, 2008 Share Posted July 1, 2008 The LEFT JOIN one should return all of the categories with their respective sub categories and the images that have the same category id, but it won't return and images that don't match the category ids. I would think that is what should be returned to display what you showed. Link to comment https://forums.phpfreaks.com/topic/112702-search-engine-problem/#findComment-579531 Share on other sites More sharing options...
solgun Posted July 2, 2008 Author Share Posted July 2, 2008 ID: 6(Category) ID: 5(Category) ID: 1(IMG) ID: 8(Category) ID: 2(IMG) ID: 10(Category) ID: 11(Category) ID: 12(Category) ID: 13(Category) ID: 14(Category) ID: 15(Category) ID: 16(Category) ID: 17(Category) ID: 18(Category) ID: 19(Category) ID: 20(Category) ID: 21(Category) this is what i've got with LEFT JOIN... IMG 1 and 2 are not suppose to be shown...(unless i open their category) ... and the rest of the IMGs(with 0 or NULL on Category) are not shown... Can you help me? ??? Thanks Link to comment https://forums.phpfreaks.com/topic/112702-search-engine-problem/#findComment-579797 Share on other sites More sharing options...
lemmin Posted July 2, 2008 Share Posted July 2, 2008 Can you try this code for me and show me what it outputs? $qry = mysql_query("SELECT A.ID, A.Categoria, A.Sub, A.Top, B.ID, B.Foto FROM categorias as A LEFT JOIN fotos as B ON B.Categoria = A.ID"); while ($row = mysql_fetch_assoc($qry)) echo "Categoria: " . $row['Categoria'] . ", Sub: " . $row['Sub'] . ", Foto: " . $row['Foto'] . "<br>"; The Sub and Foto on each line should belong to the Categoria at the beginning of the line. Link to comment https://forums.phpfreaks.com/topic/112702-search-engine-problem/#findComment-580088 Share on other sites More sharing options...
solgun Posted July 2, 2008 Author Share Posted July 2, 2008 Categoria: sub, Sub: 5, Foto: Categoria: teestega, Sub: 7, Foto: Categoria: toppp, Sub: , Foto: uploads/c71585278ea48bcf50a20e71eadbf076.jpg Categoria: sub1, Sub: 5, Foto: Categoria: teeet, Sub: , Foto: uploads/b8df15fe16f15f9c058ef981d3b38b15.jpg Categoria: fdsf, Sub: , Foto: Categoria: hdfgh, Sub: , Foto: Categoria: cxvxcr, Sub: , Foto: Categoria: twtw, Sub: , Foto: Categoria: ress, Sub: , Foto: Categoria: dfgdfgs, Sub: , Foto: Categoria: jfhgjf, Sub: , Foto: Categoria: sgdfgbv, Sub: , Foto: Categoria: dfshdgfb, Sub: , Foto: Categoria: hdfgdh, Sub: , Foto: Categoria: ghfjhnj, Sub: , Foto: Categoria: dgfhfbgfv, Sub: , Foto: This is the output ... all i want is the query to give me something like this: Categoria: fdsf, Sub: , Foto: Categoria: hdfgh, Sub: , Foto: Categoria: cxvxcr, Sub: , Foto: Categoria: twtw, Sub: , Foto: Categoria: ress, Sub: , Foto: Categoria: dfgdfgs, Sub: , Foto: Categoria: jfhgjf, Sub: , Foto: Categoria: sgdfgbv, Sub: , Foto: Categoria: dfshdgfb, Sub: , Foto: Categoria: hdfgdh, Sub: , Foto: Categoria: ghfjhnj, Sub: , Foto: Categoria: dgfhfbgfv, Sub: , Foto: Foto: uploads/b8df15fe16f15ffdsafasdfadsg8b15.jpg Foto: uploads/b8df15fe1sdgadsgadfadf981d3b38b15.jpg Foto: uploads/b8df15fe16f15f9safasdfadsfad38b15.jpg Link to comment https://forums.phpfreaks.com/topic/112702-search-engine-problem/#findComment-580194 Share on other sites More sharing options...
lemmin Posted July 2, 2008 Share Posted July 2, 2008 Ok, I guess I wasn't correct about what I thought you were looking for. How do the three images that you want relate to the categories that are returned? Or is it that you just want all images that are there? If that is the case, do you need the row that returns the images to also be able to relate back to its respective category? Link to comment https://forums.phpfreaks.com/topic/112702-search-engine-problem/#findComment-580315 Share on other sites More sharing options...
solgun Posted July 2, 2008 Author Share Posted July 2, 2008 This is the page to be viewed... "gallery.php?cat=" if Category(cat) is Null it should ask the query for categories with no Sub and Images with no category (root) if Category(cat) is "21" it returns whats inside the category 21(dgfhfbgfv) ... Images, Subcategories... if nothing is found... doens't return nothing... Link to comment https://forums.phpfreaks.com/topic/112702-search-engine-problem/#findComment-580349 Share on other sites More sharing options...
lemmin Posted July 2, 2008 Share Posted July 2, 2008 Ok, I think I understand better, now. Getting the categories with no sub categories is easy, but to have a list of images in the same query doesn't really work the same. As far as I know, you can't have the image rows appended to the end like you showed, but you can return all the images with the sub categories in the same row. The problem with this is that you will have to have extra data for every row that returns another image: Categoria: toppp, foto: uploads/b8df15fe16f15ffdsafasdfadsg8b15.jpg Categoria: toppp, foto: uploads/b8df15fe1sdgadsgadfadf981d3b38b15.jpg Categoria: teeet, foto:uploads/b8df15fe16f15ffdsafasdfadsg8b15.jpg Categoria: teeet, foto: uploads/b8df15fe1sdgadsgadfadf981d3b38b15.jpg etc... I think your best bet is to query a second time for the images unless someone else here says there is a way to do this without redundancy. Maybe try asking this question in the MySQL help section. Link to comment https://forums.phpfreaks.com/topic/112702-search-engine-problem/#findComment-580398 Share on other sites More sharing options...
solgun Posted July 3, 2008 Author Share Posted July 3, 2008 Okay im doing this function that gives me a search engine... function dosearch($tipo, $alt='', $nrresults='12') { $a=0; $page = $_REQUEST['page']; $query = $_POST['do']; if (!$page) { $page = 1; } if($page > 1){ $query = $_REQUEST['do']; } $limit = $page * $nrresults; $limit = $limit - $nrresults; if ($alt != '') { $sql_num =mysql_query("SELECT ID FROM categorias WHERE Tipo='".$tipo."' AND Sub='".$alt."'") or die(mysql_error()); $num = mysql_num_rows($sql_num); $sql_results = mysql_query("SELECT ID, Categoria, Sub, Tipo FROM categorias WHERE Tipo='".$tipo."' AND Sub='".$alt."' ORDER BY ID DESC LIMIT $limit,$nrresults") or die(mysql_error()); } else { $sql_num =mysql_query("SELECT ID FROM categorias WHERE Tipo='".$tipo."'") or die(mysql_error()); $num = mysql_num_rows($sql_num); $sql_results = mysql_query("SELECT ID, Categoria, Sub, Tipo FROM categorias WHERE Tipo='".$tipo."' ORDER BY ID DESC LIMIT $limit,$nrresults") or die(mysql_error()); } if($num < $nrresults){ $through = $num; } else { $through = $limit + $nrresults; } if ($through > $num){ $through = $total; } if($page > 1){ $from = $limit +1; } else { $from = $limit; } if($from == 0){ $from = $from +1; } if($alt != '') { $hrefalt='&cat='.$alt; } else { $hrefalt=''; } if ($page > 1) { $prevdacena ="<a href=\"$PHP_SELF?page=".($page -1)."".$hrefalt."\"><img src='images/prev.png' width='21' height='21' border='0' onmouseover=\"this.src='images/prevh.png'\" onmouseout=\"this.src='images/prev.png'\" /></a>"; } if (($num > $nrresults) && (($limit + $nrresults) < $num)) { $nextdacena="<a href=\"$PHP_SELF?page=".($page +1)."".$hrefalt."\"><img src='images/next.png' width='21' height='23' border='0' onmouseover=\"this.src='images/nexth.png'\" onmouseout=\"this.src='images/next.png'\" /></a>"; } if (($num > $nrresults) && (($limit + $nrresults) < $num)) { $resltsporpag=$page*$nrresults; } else { $resltsporpag=$num; } if ($page == 1) { $resltsporpag1=1; } else { $resltsporpag1=$resltsporpag-$nrresults; } if ($num == 0) { echo '<td>Não foram encontradas Categorias ou qualquer tipo de media.<td>'; } else { $showtdo='<td><table width="100%" border="0" cellspacing="0" cellpadding="0"><tr><td width="60%" align="left" style="padding-left:10px;" class="conteudo_baixo">'.$resltsporpag1.'-'.$resltsporpag.' de '.$num.' resultados encontrados.</td><td width="40%" align="right" style="padding-right:10px;" class="conteudo_baixo">'.$prevdacena.' '.$nextdacena.'</td></tr></table></td>'; echo $showtdo.'</tr><tr><td><table width="100%" border="0" align="center" cellpadding="0" cellspacing="0"><tr>'; } if ($tipo == 'Video') { } elseif ($tipo == 'Imagem') { $corid=0; while(list($id, $categoria, $sub, $tipo) = mysql_fetch_row($sql_results)) { $dameimg=db_arr("SELECT * FROM fotos WHERE Categoria='$id'"); $corid+=1; if ($sub == '') { $a=mysql_query("SELECT * FROM fotos WHERE Categoria='$id'"); $ab=mysql_num_rows($a); $listsubs=mysql_query ("SELECT * from categorias WHERE Sub='$id' AND Tipo='".$alt."'") or die(mysql_error()); while(list($sid, $scategoria, $ssub, $stipo) = mysql_fetch_row($listsubs)) { $a=mysql_query("SELECT * FROM fotos WHERE Categoria='$sid'"); $ab+=mysql_num_rows($a); $dameimg=db_arr("SELECT * FROM fotos WHERE Categoria='$sid' OR Categoria='$id'"); } $statsvisitas=mysql_query("SELECT ID FROM visitas WHERE Viu='$id' AND Cat='Imagens'"); $statsvisitas=mysql_num_rows($statsvisitas); $statssubs=mysql_num_rows($listsubs); $statsfotos=$ab; if ($dameimg) { $thumbas=damethumb($dameimg['Foto']); } else { $thumbas='http://cedoisracing.com/images/semimagem.jpg'; } echo '<td width="150"><table width="150" border="0" cellspacing="0" cellpadding="0" style="background-image:url('.$thumbas.');cursor:hand;" onmouseover="transp(\'cat_'.$id.'\',\'100\')" onmouseout="transp(\'cat_'.$id.'\',\'20\')" onClick="window.open(\'fotos.php?cat='.$id.'\', \'_self\'); return false"> <tr> <td height="130" valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td width="5" height="5" valign="top" align="left"><img src="images/borders-transparente_01.gif" width="5" height="5" /></td> <td> </td> <td width="5" valign="top" align="right"><img src="images/borders-transparente_03.gif" /></td> </tr> </table> </td> </tr> <tr> <td height="120" id="cat_'.$id.'" style="filter:alpha(opacity=20);-moz-opacity:.20;opacity:.20" align="center" class="conteudo_normal"> <table width="80" border="0" cellspacing="1" cellpadding="1" bgcolor="#FFFFFF"> <tr> <td width="50%" align="right">Categoria:</td><td align="left">'.$categoria.'</td> </tr><tr><td align="right">Sub-Categorias:</td><td align="left">'.$statssubs.'</td> </tr><tr><td align="right">Fotos:</td><td align="left">'.$statsfotos.'</td> </tr><tr><td align="right">Visitas:</td><td align="left">'.$statsvisitas.'</td> </tr> </table> </td> </tr> <tr> <td><table width="100%" border="0" cellspacing="0" cellpadding="0" > <tr> <td width="5" height="5" valign="bottom" align="left"><img src="images/borders-transparente_06.gif" /></td> <td> </td> <td width="5" valign="bottom" align="right"><img src="images/borders-transparente_07.gif" /></td> </tr> </table></td> </tr> </table></td> </tr> </table></td> '; if ($corid == 4) { echo '</tr>'; $corid=0; } else { echo '<td> </td>'; } }} } elseif ($tipo == 'Sistema') { } elseif ($tipo == 'Projecto') { } echo '</tr></table></td></tr>'.$showtdo; } The problem is that i can't find a way to merge the query for images and the query for categories so the result on page flipping can work... Link to comment https://forums.phpfreaks.com/topic/112702-search-engine-problem/#findComment-580601 Share on other sites More sharing options...
solgun Posted July 3, 2008 Author Share Posted July 3, 2008 Anyone? i want to make this function work as a search engine... Thanks Link to comment https://forums.phpfreaks.com/topic/112702-search-engine-problem/#findComment-581252 Share on other sites More sharing options...
solgun Posted July 6, 2008 Author Share Posted July 6, 2008 Can't be done? Link to comment https://forums.phpfreaks.com/topic/112702-search-engine-problem/#findComment-582631 Share on other sites More sharing options...
solgun Posted July 7, 2008 Author Share Posted July 7, 2008 nvm... i've got it working using other way... Thanks anyways Link to comment https://forums.phpfreaks.com/topic/112702-search-engine-problem/#findComment-583315 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.