not getting all desired MySQL output via PHP

[dataDude]

Hi all, it's been awhile since I've been on here and now I'm in such a pickle. I'm creating a website that partly deals with client entry security. Well, I have some free hosting (to use until I'm ready to be completely up and running), the database is up and I have the coding knowhow, but the effect isn't working the way I want it to. I've even put the SELECT statement directly into phpMyAdmin to make sure I'm stating it correctly, and sure enough I get all the results I want. Here's what's going on:

$query = "SELECT privacy FROM Entries WHERE loginID='$loginID' AND id<'$number' AND privacy='1' OR privacy='3' ORDER BY loginID DESC LIMIT 10";

$result = mysql_query($query, $connect) or die(mysql_error());

while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
$privacy = $row['privacy'];

echo "<table width=\"100%\" border=\"1\" bgcolor=\"white\">";
echo " <tr>";
echo " <td>";
echo "<table width=\"100%\" align=\"center\">";
echo " <tr>";
echo " <td><font size=\"-1\"><b>";
switch($privacy)
{
case '1':
echo "Public";
break;
case '2':
echo "Private";
break;
default:
echo "Friends only";
}
echo "</b>    ";
echo " </tr>";
echo "</table>";
echo " </td>";
echo " </tr>";
echo "</table>";
echo "<br>";
}

Note: I've omitted some erroneous information to get to the guts of the situation. So, as you can see, depending on how each entry has already been, well, entered, only entries with privacy set to "1" or "3" will be seen. I've already entered some test data (18 entries altogether). Entries 18 and 17 are set at privacy='3', then 16 has privacy set to privacy='2', and then the rest are set to privacy='1'. With the exception of entry 16, they should all be showing with the query given, yet every time I make a correction and upload it to the server and then refresh, only entries 18 and 17 show, but I know that 15 and below should also be showing. Anyone see something that I don't?

[dataDude]

I don't think it would have mattered, but I realized that I had left out the "break;" part of the default section of the switch statement. Sure enough, it had no effect. Still open to advice/suggestions. Thanks.

[Force Flow]

You're missing + signs in your string for $query.

You can add them with or without spaces:

$mystring =  'this is part of the string' + $variable + 'this is the rest of the string';
$mystring =  'this is part of the string'+$variable+'this is the rest of the string';

also, do an echo statement on the $query variable to check to make sure the query that is generated is correct.

echo $query;

[dataDude]

Thanks for your thoughts, Force Flow. However, PHP string concantenation requires a period and not a plus sign. On the plus side, your second statement made a world of difference and I figured out that the wrong loginID variable was being referenced and therefore the id variable was wrong. It has since been corrected.

With that in mind, I've stumbled across a related problem of which I've come up with a solution that I don't want to stick with and I know it's not the only one. In that page, I want to list 10 entries, and that page will have a listing for different pages on top, for example:

<< Previous 1 2 3 Next >> ...I think you get the idea.

For each page number that is clicked on (or "previous" or "next"), I want to list 10 entries to the page. However, with the query saying to LIMIT 10 but asking for privacy of "1" and "3" it will give 10 entries minus the number of entries that have been set as "private" or "2". This means that, theoretically, there could be as many as 9 or 10 entries available on a page for viewing or as few as 1 to a blank page. I hope you all can see my dilemma. It confused me for several minutes. I've so far resorted to getting all 10 entries per page, including all privacies '1' '2' and '3', and for the entries marked '2' I've simply said that that entry is unreadable. With the public the way it is, though, it's understandable that anybody who sees that a message is unreadable could get suspicious and possible resentment. I want to avoid that but I can't think of how to correct it.

If that all makes sense, I would appreciate any help with this. Thanks.

[Force Flow]

Originally Posted by dataDude Thanks for your thoughts, Force Flow. However, PHP string concantenation requires a period and not a plus sign. On the plus side, your second statement made a world of difference and I figured out that the wrong loginID variable was being referenced and therefore the id variable was wrong. It has since been corrected. With that in mind, I've stumbled across a related problem of which I've come up with a solution that I don't want to stick with and I know it's not the only one. In that page, I want to list 10 entries, and that page will have a listing for different pages on top, for example: << Previous 1 2 3 Next >> ...I think you get the idea. For each page number that is clicked on (or "previous" or "next"), I want to list 10 entries to the page. However, with the query saying to LIMIT 10 but asking for privacy of "1" and "3" it will give 10 entries minus the number of entries that have been set as "private" or "2". This means that, theoretically, there could be as many as 9 or 10 entries available on a page for viewing or as few as 1 to a blank page. I hope you all can see my dilemma. It confused me for several minutes. I've so far resorted to getting all 10 entries per page, including all privacies '1' '2' and '3', and for the entries marked '2' I've simply said that that entry is unreadable. With the public the way it is, though, it's understandable that anybody who sees that a message is unreadable could get suspicious and possible resentment. I want to avoid that but I can't think of how to correct it. If that all makes sense, I would appreciate any help with this. Thanks.

Oops, glad you caught my error. Been working too much in javascript lately

With LIMIT, you can specify a starting number and an ending number, like this:

LIMIT 1, 10
LIMIT 11, 21
LIMIT 22, 32

And I think you can do this too:

LIMIT 1, 10
LIMIT 11, 10
LIMIT 22, 10


This might be worth a look: http://www.plus2net.com/php_tutorial/php_paging2.php

[dataDude]

I appreciate your input, Force Flow. That is another path I will keep in mind. In the meantime, right before I read your reply, I was able to solve the problem using an associative array. My method, unfortunately, is a bit inefficient in that it takes all the id's with '1' and '3' privacy and puts this into the array and then chooses which array variables to work with in a given page. Fortunately, I see that the technique I used will go very fast for the first, probably, hundred or so entries per user, and I expect the time taken to get that far will be a significantly long time. Until it gets to that point, I have time to think of a more efficient method of performing the task.