[SOLVED] MySQL query question

[Andy17]

Hey guys!

 

I have a website where the users are able to browse jokes that are taken out from my MySQL database. It all works, except I'd have to create multiple MySQL tables - one for each joke category. Right now I am doing the following:

 

<?php

//$number is stored in the page URL. So jokes.php?id=5 would result in row 5 being displayed on the page.
$sql1 = "SELECT * FROM table WHERE id = '$number'";
$result = mysql_query($sql1);


if ($result)

{

$count = mysql_num_rows($result);


if ($count == 1)

	{

                         // The content is being displayed here

                }

}

?>

 

That's the most important part. Anyways, I'm looking to have ALL of my jokes put into a single table, so I need to sort them in my query. I was thinking of doing this:

 

<?php

// Now the appropriate rows are selected, but I want them sorted as you will see in an example below.
$sql1 = "SELECT * FROM table WHERE category = 'Adult' AND status = '1'";
$result = mysql_query($sql1);


if ($result)

{

$count = mysql_num_rows($result);


if ($count == 1)

	{

                       // Displaying content here

                }

}

?>

 

However, if my table looks like this, it will result in a blank page in the middle:

 

id     |  category       |  status
----------------------------------
0     |  Adult           |  1
1     |  Blonde          |  0
2     |  Adult           |  1
3     |  Adult           |  0

 

So, my question is how I can prevent these blank pages. Let's say I want to run my query on the table above - then I'd like to have it sorted like this before running the query (or query 2/2 I assume):

 

id     |  category       |  status
----------------------------------
0     |  Adult           |  1
1     |  Adult           |  1

 

If I could sort my table like that, I wouldn't get those blank pages. What would be awesome is if I could get the results from $sql1 (see code box 2) arranged with new id numbers (0, 1, 2, 3, 4 instead of 0, 2, 3, 7 for example) and access those results in a new query, which would look like this:

 

<?php

// I don't know how to access results from a previous query, so I'll just write $sql1_results
$sql2 = "SELECT * FROM $sql1_results WHERE id = '$number'";
$result = mysql_query($sql2);

?>

 

I hope you get my point. If not, please ask. Any suggestions?

 

Thanks!

 

PS - I'm fine with not using LIMIT - I just want it to work.

[Adam]

You're not making a lot of sense - "it will result in a blank page in the middle", ey? what do you mean blank pages? Like for example if there was no id=5 and you've gone to .php?id=5, it would be a blank page? Could just not link to them? or to stop the pages being blank perhaps output an error?

 

Wouldn't be much use in storing them in another query to be honest, don't think it's possible to sort of temporarily save the results and access them in another query without literally saving them in another table? But that would be pointless? Could store them in an array if you really wanted though?

 

An when you say sorted, do you mean so the IDs are all sort of flowing ie. 0 1 2 3 4 5 6 7 ... or do you mean by category? If its by category could just use:

 

ALTER TABLE tableName ORDER BY category

 

If it's flowing I'm not a 100% but I don't think it's possible with a simple query? But could loop thru the results and sort of rearrange them that way, probs get a little messy but would be a soloution..

 

Try and explain things better..

 

Adam

[Andy17]

Ok here is my script in action:

 

http://menu.jokeheaven.eu/jokes/adult.php

 

If we take a look at the following example again:

 

id    |  category        |  status
----------------------------------
0     |  Adult           |  1
1     |  Blonde          |  0
2     |  Adult           |  1
3     |  Adult           |  0

 

I would use the following query:

 

<?php

$sql1 = "SELECT * FROM table WHERE category = 'Adult' AND status = '1'";
$result = mysql_query($sql1);

?>

 

$result would now store row 0 and 2 (id 0 and 2). However, if I were to visit jokes.php?id=1, it would look for row 1 in $result, but that row doesn't exist since only row 0 and 2 is stored. That's why I need to find a way to sort the id numbers in $result so they would look like this: 0, 1, 2, 3, 4 instead of 0, 2, 5, 9 for example.

 

As you noticed if you visited my site, the id in the URL is incremented/decremented when a user presses a button. This is why I need the id numbers in my result sorted like 0, 1, 2, 3 instead of 0, 4, 7, 9 (or else nothing would be shown when a user visits jokes.php?id=1/2/3/5/6/8).

 

How would you use an array for this? I am not too experienced with arrays.

[Andy17]

Bump.

[Adam]

I imagine then that your just sort of incrementing the current ID by 1 for the "next" button?

 

Seen as the IDs don't match up and you can't always guarantee that they will all be flowing one after another, you could try something more like:

 

<?php

$record = (int) $_GET['record'];

$query = mysql_query("SELECT * FROM table LIMIT {$record}, 1");

//...

?>

 

That would return the first, second, third record or whatever from the table.. obviouslly it won't always match the ID, but you could still do a check for that.. like if they go to .. adult.php?id=3 .. then you select it by the ID, or if its .. adult.php?record=2 .. then you select it by the record number or something? I don't know but it's a an idea, would allow you to keep the "next" button feature...

 

Adam

[Andy17]

Bump. I'm not entirely sure what you mean, Adam.

[discomatt]

To grab the next row in a database... assuming you're using an auto increment column called 'id' and $id holds the current id being displayed ...you would use

 

$q = " SELECT `id`, `joke` FROM `jokes` WHERE `id` > $id LIMIT 1 ";

 

If you're getting bad results, you may want to add an ORDER BY `id` in there, but it shouldn't be needed ( unless you're screwing around with id values )

[Andy17]

Thanks a lot discomatt; I edited your code a little and made a few adjustments to my code and now it seems to be working. I haven't tested it thoroughly yet, but everything should be OK. If not, you'll probably just see a new topic by me if I cannot figure it out myself.

 

You just got me a huge step closer to completing my website. Thanks A LOT - to everyone else who tried to help, too!