Getting MySQL results

PHP
1

This may be a silly question but what am I doing wrong here:

$query="select * from albums, categories_map where enabled=1 and deleted=0 ". if (!empty($_POST[$pop])) { echo ." and category_id = 2 ". } ." order by rand()";

I’m trying to show results dependant on which options are checked on a previous page. That form is then sent using POST

2

What messages (error or otherwise) are being presented? Or is the query just not returning any records?

V/r,

:slight_smile:

3

You’re selecting from multiple tables. The query needs to know what table “enabled”, “deleted”, and “category_id” belong to.

Also, ordering by rand is really expensive query-wise. Is there a reason you need to randomize it here instead of in PHP?

4

I was getting a syntax error so I rewrote the code but now I get nothing at all.

This is what I’ve now got:

$sql_select = "SELECT * "; 
$sql_from = " FROM albums, categories_map "; 
$sql_where = " WHERE enabled=1 and deleted=0";
$pop=$_POST['pop'];
$classical=$_POST['classical'];
$rock=$_POST['rock'];
$country=$_POST['country'];
$indie=$_POST['indie'];
$easy =$_POST['easy'];
if ($pop =="on"){ 
$sql_where .= " AND category_id=1 "; 
} 
if ($classical =="on"){ 
$sql_where .= " AND category_id=2 "; 
} 
if ($rock =="on"){ 
$sql_where .= " AND category_id=3 "; 
} 
if ($lean=="on"){ 
$sql_where .= " AND category_id=4 "; 
} 
if ($indie =="on"){ 
$sql_where .= " AND category_id=5 "; 
} 
if ($easy=="on"){ 
$sql_where .= " AND category_id=6 "; 
} 
	$query=$sql_select . $sql_from . $sql_where;
    $albums=dbselect( $query,"dbLinkInt" );

var_dump($_POST[‘pop’]); gives me string(2) “on” but var_dump($albums); gives me NULL

I’ve also removed the rand because it wasn’t really needed

5

It’s been a LONG time since I’ve really worked with PHP. Do you have a query analyzer or similar that you can check this query on? To make sure the data is there.

:slight_smile:

6

I don’t have anything like that, but if I leave the additional ’ AND category_id= ’ queries out then it works. Also if I put the query into phpMyAdmin then I get the results too.

I think the problem is with this part:

$pop=$_POST['pop'];
$classical=$_POST['classical'];
$rock=$_POST['rock'];
$country=$_POST['country'];
$indie=$_POST['indie'];
$easy =$_POST['easy'];
if ($pop =="on"){ 
$sql_where .= " AND category_id=1 AND albums.id = categories_map.id"; 
} 
if ($classical =="on"){ 
$sql_where .= " AND category_id=2 AND albums.id = categories_map.id"; 
} 
if ($rock =="on"){ 
$sql_where .= " AND category_id=3 AND albums.id = categories_map.id"; 
} 
if ($lean=="on"){ 
$sql_where .= " AND category_id=4 AND albums.id = categories_map.id"; 
} 
if ($indie =="on"){ 
$sql_where .= " AND category_id=5 AND albums.id = categories_map.id"; 
} 
if ($easy=="on"){ 
$sql_where .= " AND category_id=6 AND albums.id = categories_map.id"; 
}

although var_dump($pop); gives me string(2) “on”

7

Is category_id an INT, BIGINT, TINYINT, or a string?

phpMyAdmin has a Query Analyzer. :smile:

V/r,

:slight_smile:

8

category_id is an INT

oh yeah, I didn’t realise that about phpMyAdmin

9

Curious. If you uncheck ‘Pop’, then check ‘Classical’, submit and var_dump($pop), what does it say?

:slight_smile:

10

That says the same, I changed the ==“on” to <>“” and it now works. :slight_smile:

11

That depends on the number of records to be processed and the number you want to extract from the random list.

If extracting one record from the result it is actually more efficient up to about 700 records. If you are extracting 30 or more then it is more efficient up into the 100000 range.

That it is really expensive is only true if you want a very small number of records from a really huge number of possibilities.

12

I didn’t know it had any effect on the query at all to be honest. I’ll be getting thousands of results and want the top five to be always the top five so I guess something like order by top_5 rand () would be best, with anything not being in the top_5 field being randomised?

13

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.