Excluding Rows in PHP?

[Mike]

Hi all,

Does anyone know if you can exclude rows in PHP? Like do something like:



$result = mysql_query("SELECT * FROM table EXCEPT ROW1 AND ROW2");


Obviously that wouldn't work (I don't think:)), but could anyone tell me if there is a method like that?

Cheers,
Mike

[emke]

this is the first thing that hit me...


SELECT * FROM table WHERE id<>5

basically what this does is that it takes into account all the ids below 5 and over 5 but not id=5.

I hope you get it as I'm not good at explaining things like this.

[Mike]

So it basically just ignores the fifth row? You'd write it if you didn't want row 5 to be included?

Also would you have to change id to anything?

Thanks,
Mike

[emke]

Yes, it ignores the id=5.

[Mike]

Thanks emke:)

[Stevens]

On the other hand, what would be the easiest way to say...get the first two rows returned.

I thought at first that I'd have to throw in the mysql_fetch_array() function to select the first two results of a query (ordered by date desc), but I'm having problems getting the result set to show correctly. By using either the numeric indices or the field ID association, I get defined fields for each row returned, but what I really need is to get the associative fields in each row AND limit the result set to the two most current entries.

I thought the above function would do the trick if manipulated right, but now that I read this post I thought there might actually be a solution in the query that will return only the wtwo records I need...any ideas?

ERIC

[MarkB]

why not try:

SELECT field1, field2 FROM tbl_whatever LIMIT 2

?

[Chris]

To order it by date and limit it (Assuming you have a "Date" field).

select * from table order by date desc limit 2

With limits you can also do sets... thats how they do search results (click to see the next ten results).

select * from table limit 10,10

Would return 10 results after the 10th result. So results 11-20

[Stevens]

Hey, thanx, that's EXACTLY what I need! Now when I issue the

while ($row = mysql_fetch_array ($result, MSSQL_ASSOC))

function,

$row[field1]
$row[field2]
$row[field3]
$row[field4]
$row[field5]
$row[field6]

variables are "assigned." Printing out all the arrays with the loop is a cinch, but I would need to do the SQL "limit 2" thing mentioned earlier, but I'll also need to be able to call each array separately. I have two separate table fields that need to hold these two records so issuing a running a loop will not work. Any idea how I can separately call the two rows separately?

[Chris]

I'm not sure if I understand the question.

[Stevens]

I am only used to returning all rows (i.e. $row[0], $row[1], etc.) for each array returned with the mysql_fetch_array() function.

What I need to do is, after I have returned a SQL result set limited to two arrays, distinguish the difference between the first array returned and the second array returned so I can properly place the returned info where it needs to be...in different table cells throughout the page...ugh...I'm trying here...

ERIC

[kdb003]

can you be more specific to your actual problem. you have 2 arrays like $row[] and $other[] and what are you trying to do with them?

[Stevens]

Well, when you return a result set with SQL, limit two rows, you get a "table" with 2 rows and maybe 5 columns, fo rexample. It's easy to run a loop that returns each row one at a time until the end of the result set is reached.

I have an HTML table that I need to return the data of each row separately. I need to display the data for row0 to one part of the table and row1 to another part of the table. How do I differentiate between the two returned rows of data and define them so I can place each individual piece where it needs to be for correct formatting?

I guess I'm having trouble explaining...

Go to www.thecolumbusgroove.com/new-groove.php and you'll see two blank blue areas that need each data set.

[eMEraLdwPn]

i'm not entirely sure i understand what you want, but i think you could go about it two ways

1 would be to do the query, then
$x=0;
while($row = mysql_fetch_array($query)){
$var1[$x] = $row[0]
$var2[$x] = $row[1]
$var3[$x] = $row[2]
....
$x++;
}
so you can save all the variables for later

if you're doing it for only 2 rows though, the easier way would probably just to be call the query once with LIMIT 1 and then after you output that call it again for the second row with LIMIT 1, 1 which would return the second result.

[Stevens]

I think the second way would work best. Would I have to clear the memory or something before I call the second row?

In the first method you explained, can you give me a breakdown of what is going on with all the variables...maybe I have them confused...

What I think is going on is $row[0] is the variable for the first field returned in the array, so on and so forth...right? What does the $x++ mean...I see that all the time...

[Stevens]

Does the $x++ add 1 to the current value of x? If that's the case it would essentially give me

$firstrow['firstfield']
$firstrow['secondfield']
$firstrow['thirdfield']

then after $x increases by 1,

$secondrow['firstfield']
$secondrow['secondfield']
$secondrow['thirdfield']

Does that sound right?

[eMEraLdwPn]

if you were doing SELECT f1, f2, f3 FROM table and then while(mysql_fetch_array($query))

$var1[$x] = $row[0] means $var1[0] would hold f1, $var2[$x] = $row[1] means $var2[0] would hold f2

after it stores all the f1, f2, f3 in $var[0], it would do $x++ (increments x by 1), and it would go through the loop again setting $var1[1] = $row[0], $var2[1] = $row[1]

that method is really only necessary if you're going to be doing something with a lot more rows, if it's only 2 the second way is definitely best

[Stevens]

Thanx, eMEraLdwPn

In the case I'm working on now, I only need two rows, but I'll have places later where I'll need hundreds of them. Thanx a bunch...that's exactly what I need. I suppose 1 query to the server beats two, right?

If I define the variables you mentioned above, would they hold their definition if I ended the embedded script, changed to html, then started another script later in the page? Correct me if I'm wrong, but a variable is a variable until the end of the page, not the end of the script, right?

ERIC

[r2d2]

Yep, you can close the script, and the variables will stay in mem for when you open it again.