Select from previous query?

[Andy17]

Hey guys!

 

I have a website where users are able to browse jokes by clicking a previous or next button to do a new MySQL query. Click the link below for a test:

 

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

 

I am selecting the row with id = id in URL. So, if the URL is ...adult.php?id=2, the row with id = 2 is displayed. My query looks like this:

 

<?php

// Establishing MySQL connection
mysql_connect("db_server", "db_username", "db_password") or die(mysql_error());

// Selecting database
mysql_select_db("db_name") or die(mysql_error());

// Finding the row with the number as id - if it exists
$sql1 = "SELECT * FROM jokes WHERE id = '$number'";
$result = mysql_query($sql1);


if ($result)

{

$count = mysql_num_rows($result);


if ($count == 1)

	{

                      // Content is displayed here

                }

}

?>

 

The thing is that I have jokes in many categories and instead of creating 10 different tables (wouldn't be a problem, but would make my joke validation script HUGE), I would like to keep it all in one table. So, I need a query to select a given category ("Adult" for instance) where "status" is 1. I was thinking something like this:

 

<?php

// Establishing MySQL connection
mysql_connect("db_server", "db_username", "db_password") or die(mysql_error());

// Selecting database
mysql_select_db("db_name") or die(mysql_error());

// Finding the row with the number as id - if it exists
$sql1 = "SELECT * FROM jokes WHERE category='Adult' AND status='1'";
$result = mysql_query($sql1);

/*
Now the part cannot really figure out; the result is obviously stored in $result, 
but how do I use $result for a new query where I select the row where id = id in URL. 
Let's say that the URL is ...adult.php?id=5 - then I would want to display row 5 from $result. 
So normally a query could look like this: "SELECT * FROM table", but instead of FROM table, 
I want to SELECT * FROM $result but I'm not sure how to do that. Logically, this would look
something like this (I know it doesn't work, but maybe it helps you understand what I'm getting at).
*/

mysql_query("SELECT * FROM '$result' WHERE id = '$number'");

I'm fully aware that it doesn't work but is there a way to go about it?

 

I'm sorry if this made no sense at all, I don't blame you. It's actually quite hard to explain. I hope some of you get my point anyhow. I want to do this to make it easier for me to check for newly added jokes where status = 0 cause it's easier to do this if all my jokes are in 1 table instead of 10.

[corbin]

Hrmmm, limit might work.

 

 

Eg:  SELECT * FROM table jokes WHERE category = 'Adult' AND status = 1 LIMIT 4, 1;

 

 

That would give you the 5th result.

[Zane]

just to save you from future headaches......

-------------------------------------------

 

create yourself another table....called cat (for categories)

lay it out like this or something

id   | name
---------------
0    | general
1    | adult
etc...
etc..
etc....

 

then on your jokes table.....add another column to it called cat as well...

and put the id for the type of joke it is..

so all the adult ones will be 1 for instance....

 

Then you can just go

$sql1 = "SELECT * FROM jokes WHERE category=1 AND status=1;

 

Now the part cannot really figure out; the result is obviously stored in $result,

but how do I use $result for a new query where I select the row where id = id in URL.

Let's say that the URL is ...adult.php?id=5 - then I would want to display row 5 from $result.

So normally a query could look like this: "SELECT * FROM table", but instead of FROM table,

I want to SELECT * FROM $result but I'm not sure how to do that. Logically, this would look

something like this (I know it doesn't work, but maybe it helps you understand what I'm getting at).

 

you don't select things from the result if you're just going to do another query....with a perfectly good SQL statement anyway.

what I mean is, if you already have the id.  there's no reason to select all the jokes and then select the id

 

maybe I'm not sure of what you're trying to do, but whatever it is....it is much easier than you believe it is......I can sense it.

explain a little more...if I have lead you wrong

[Andy17]

Basically I just want to have all of my jokes inside one table instead of one for each category. However, this obviously requires that I have a way to sort the jokes. Let's say that my "jokes" table looks like this (the categories can, as you mentioned, be a number instead):

 

id    | category    | status
-------------------------
0     | Adult         | 1
1     | Blonde       | 0
2     | Adult         | 0
3     | Sports       | 1
4     | Adult         | 1
5     | Adult         | 1

 

Then I would like it to select row 0, 4 and 5 - but instead of storing the result as row 0, 4 and 5, I'd like it to store them as 0, 1 and 2 instead. This is because my script uses the following query to display jokes (because I'm using $id from the page URL (page 4 = $id =4)):

 

mysql_query("SELECT * FROM jokes WHERE id = '$id'");

 

So if I were on page 2, nothing would be found in my MySQL table because the stored rows are 0, 4 and 5 (from the example above). Is this possible?

 

My script works perfectly but I want to make a script where I can validate submitted jokes before putting them on my website. This would give me a headache if I have to check 10 different tables (assuming I have 10 categories) for rows where status = 0.

 

I hope I explained myself better.

[aschk]

I think you're getting confused.

 

The query to get ALL the "adult" category jokes with "status" of 1 is

SELECT * FROM <table name here> WHERE category = 'Adult' and status = '1'

 

In order to "page" the results you need to apply limitations on which slice of the result query you bring back.

 

So, if you're displaying 10 per page, you need to have a page parameter in your url (i.e. page=2) NOT an id parameter.

Then if you page parameter is 2, and you're displaying 10 results per page, you want the results between 10 and 20 (from the query we got).

 

Thus, you have something along the lines of the following:

<?php

$page = $_GET['page'];
$category = $_GET['category'];
$status = 1;

if(! $page > 0 ){$page =1;}

$limit = ($page - 1) * 10;

$sql = "SELECT * FROM jokes WHERE category = '%s' and status = '%d' LIMIT %s, 10";
$query = sprintf($sql, $category, $status ,$limit);

$result = mysql_query( $query );
while($row = mysql_fetch_assoc($result)){
  $data[] = $row;
}

echo "<pre>";
print_r($data);
echo "</pre>";
?>

 

Obviously some things need to happen in the above. Your database connection needs to occur (you have done this in your script so just use that). And all it does at present is fill an array ($data) with echo it out into your screen (so you can see what output you're getting).

 

So try it out with something like

mypage.php?page=1&category=Adult

[Andy17]

Hey aschk and thank you for your answer.

 

You slightly misunderstood me, but it doesn't matter too much, since I think I can use your code - when I get it to work that is. Are you sure that the following piece of your code is correct?

 

while($row = mysql_fetch_assoc($result))

{

      $data[] = $row;

}

 

I'm getting no output printed, so I put an echo inside that while loop and it turned out that it never runs what's inside those brackets. It's been a long time since I have worked with while loops, so please bare with me.

[Andy17]

Bump. Last thing I need to be able to finish my website.

[dropfaith]

you can use this edited to your site this is just a quick one i use alot

 

<?php
// includes the host and username detials
include("template/conf.php");
// open database connection
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");
// select database
mysql_select_db($db) or die ("Unable to select database!");
// generate and execute query				
$query = "SELECT * FROM gallery WHERE category = 'Adult' and status = '1' ";				
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
// if records present
if (mysql_num_rows($result) > 0)
{
// iterate through resultset
// print article titles
while($row = mysql_fetch_object($result))
{
?><!-- echo your row data out here in a while loop -->
<? echo $row->Photographer; ?>
<?php
}
}
// if no records present
// display message
else
{
?>

			<?php
			}
			// close database connection
			mysql_close($connection);
			?>