Search Engine Problem

solgun · 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

br0ken · 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");

?>

solgun · 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

solgun · 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

solgun · July 1, 2008

if you don't understand please ask me to be more expecific

lemmin · 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"

solgun · 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

lemmin · 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.

solgun · July 1, 2008

i don't get the left join, the query is still not giving me the images where Category is '' or '0'...

lemmin · 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.

solgun · 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.

lemmin · 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.

solgun · 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

lemmin · 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.

solgun · 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

lemmin · 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?

solgun · 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...

lemmin · 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.

solgun · 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...

solgun · July 3, 2008

Anyone? i want to make this function work as a search engine...

Thanks

solgun · July 6, 2008

Can't be done? :-[

solgun · July 7, 2008

nvm... i've got it working using other way...

Thanks anyways

Sign In

Search Engine Problem

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information