Two Queries Help

[ballouta]

Hi,

 

I have two queries I have problems with:

 

1) I have 'books' table, with the following fields:

 

ncode, bookname, author, language, category, isbn

 

I want to select all books in this table but I want to show the author 'geroge' books first in language 'En' followed by 'geroge' books also but in language 'Fr', then all other rows resulted.

 

2) The second question is that I need to query the above 'books' table, i already selected distinct(category) and printed out the result, till here everything is ok BUT i want to show one of these categories on top before any other category. Is this possible?

 

Thank you in advance

[genericnumber1]

you could just use the "ORDER BY author, language" clause for the first one.

 

for 2 could you be a little more specific? what qualifications does this category need to match? perhaps post your existing code?

[ballouta]

thanks

regarding the first query

 

if i write:

<?php
$query = "SELECT * FROM `books` WHERE `cat` = '$cat' and `language` = 'En' Order by author, language ";
$result = mysql_query($query);
?>

How the code will know that i want the author 'george' and language 'En'?

 

[bobbinsbro]

1) try:

$sql = "SELECT * FROM books ORDER BY author, language";

 

then retrieve the results (i'll assume they are saved into an array named $results) and display them like this:

 

//display only geroge's books: find the first result for geroge, and since the array is ordered by the sql,
//the next chunck will all be geroges books, until you hit a new author, and there you stop.
for($i = array_search('geroge', $results['author']); $results['author'][$i] == 'geroge'; ++$i){
//display book info. i assume 'fr' books come immediately after 'en' books... you know... a,b,c,d,E,F
}

//display all other authors
$loopCount = count($results);
for($i = 0; $i < $loopCount; ++$i){
        if ($results['author'][$i] != 'geroge'){
             //display book info.
        }
}

 

2) a similar solution to #1 would probably work.

 

 

i hope there's a better way than this, as it's a bit clumsy.  :-\

[genericnumber1]

@ballouta

That merely orders by author, and then suborders each author by language. If you are just selecting by a certain language you don't need to order by language because.. well.. they're all the same. Same with author. If you just want george and en then

 

SELECT * FROM books WHERE author = 'george' AND language = 'en' will suffice.

[ballouta]

@genericnumber1 you are right about the language, but in fact i have to remove from the query posted (where language ='En'), i wrote it by mistake.

 

@bobbinsbro

i have to try your code and come back to you

 

then i will continue with my second query to solve it

 

Thank you all for your help

[bobbinsbro]

i noticed a problem with the code i posted  :-\ :

since it will probably be easiest for you to mysql_fetch_assoc() into a $results[] array, this condition:

$results['author'][$i] == 'geroge'

should actually be:

$results[$i]['author'] == 'geroge'

 

(since $results will have numeric keys).