problem looping through database fields

[surreal5335]

I am trying to loop through a series of database fields and have print out on the web page. My one function that prints out just a specific group of fields works fine:


/**
* returns array of a post from database
* @returns array
*/

function find_post($id) {

$connection = db_connect();
$query = sprintf("select posts.title, posts.body, posts.user_id, users.name
from
posts, users
where
posts.user_id = user_id and posts.id = %s",

mysql_real_escape_string($id)
);

$result = mysql_query($query);

$number_of_posts = @mysql_num_rows($result);
if ($number_of_posts == 0) {
return false;
}

$row = mysql_fetch_array($result);

return $row;
}



But my function that has problems getting the data from the database is:


/**
* returns array of posts from database
* @returns array
*/

function find_posts() {

$connection = db_connect();
$query = 'select posts.title, posts.body, posts.user_id, users.name
from
posts, users
where
posts.user_id = user_id';

$result = mysql_query($query);

$number_of_posts = mysql_num_rows($result);
if ($number_of_posts == 0) {
return false;
}

$result = result_to_array($result);

return $result;
}


While I am not getting any errors, I am also not getting any data from my database either

I have already had a problem with this function about the function it calls within it:


function result_to_array($result) {

$result_array = array();
for ($i=0; $row = @mysql_fetch_array($result); $i++) {
$result_array[$i] = $row;
}

return $result_array;
}


While I am feeling good about being able to fix a majority of my errors, this one I am not sure about.

I appreciate any help in the matter

[1andyw]

Should this be altered fromwhere
posts.user_id = user_id
to
posts.user_id = users.user_id

Andy

[surreal5335]

Thanks for the help Andy,

I tried your suggestion, although I recieved a warning that says

mysql_num_rows(): supplied argument is not a valid MySQL result resource

which was gathering info from the variable $query, so I am guessing that it didnt like the change. Besides, that alteration you suggested from the original is also in my other function find_post() which does work fine.

I look forward to any other suggestions you may have.

[Old Pedant]

That error means that $result is, indeed, not valid.

Since you are COMPLETELY IGNORING any error that you might be getting from
$result = mysql_query($query);
that most likely means that you still have an error there.

How about doing an "or die" and finding out what the error is?

[surreal5335]

I tried the or die suggestion and I still have not recieved any errors printed out on the page.

this is what I have so far:


$result = result_to_array($result) or die(mysql_error());



I looked into my error log and this is what I have :


[warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request function

[error] [client 68.175.123.210] Invalid method in request \x16\x03\x01

[error] [client 68.175.123.210] File does not exist: /usr/local/apache/htdocs/501.shtml

[warn] RewriteCond: NoCase option for non-regex pattern '-f' is not supported and will be ignored.


Thanks a lot for the help

[Old Pedant]

Doesn't look like any of those came from the die.

Maybe add something to the die to identify it??


$result = result_to_array($query) or die("SQL failure: " . $query . ", error: " . mysql_error());
Or something along those lines?

[surreal5335]

I tried the suggestion, I see what your going for. this is the ouput I got from it:

SQL failure: select posts.title, posts.body, posts.user_id, users.name from posts, users where posts.user_id = user_id, error:

sadly no error is still showing up from the mysql_error()

I tried it both with:


$result = result_to_array($query) or die("SQL failure: " . $query . ", error: " . mysql_error());



$result = result_to_array($result) or die("SQL failure: " . $query . ", error: " . mysql_error());

the $query gave some sort of output the one you read above, while the other was blank. I really wish I had an idea of what to do, but I dont.

Thanks a lot for the help

[Old Pedant]

Oops...sorry about that. Yeah, the $query was wrong.

Well, you are out of my depth. I'm not a PHP user. Sorry.

[bazz]

I am dying with manflu right now so I haven't read all posts.

However, in your working query in post 1 you have this:


$number_of_posts = @mysql_num_rows($result);


Then in your busted query you have this:


$number_of_posts = mysql_num_rows($result);


Spot the difference?? Also, if you assign the alias to user_id from one table, you need also to do so for the other table so andy's suggestion is spot on.

And if you use more up to date query syntax it may be easier to troubleshoot.

so in a nutshell,


function find_posts() {

$connection = db_connect();
$query = 'select
p.title
, p.body
, p.user_id
, u.name
from
posts as p
inner
join users as u
where
p.user_id = u.user_id';

$result = mysql_query($query);

$number_of_posts = @mysql_num_rows($result);
if ($number_of_posts == 0) {
return false;
}

$result = result_to_array($result);

return $result;
}


I don't do php much so I am hoping you have it done OK.

btw, your post #3 contains what pointed me to the issue.

mysql_num_rows(): supplied argument is not a valid MySQL result resource

That says that my_sql_num_rows() is not valid.

bazz

[Old Pedant]

Quote:
mysql_num_rows(): supplied argument is not a valid MySQL result resource

That says that my_sql_num_rows() is not valid.

Ummm...even from what little PHP I've done, I don't think that is true, Bazz. I think it is saying that the *ARGUMENT* to mysql_num_rows() is not valid. In fact, that's precisely what it says: "not a valid MySQL result resource".

So that says clearly, to me, that in the line
$number_of_posts = mysql_num_rows($result);his $result is *NOT* a MySQL "result".

[bazz]

Yep Old P, I see what you mean. apologies for getting ahead of myself - one who isn't so hot on php either.

I do think though that I would try the


$number_of_posts = @mysql_num_rows($result);


with the @ when the OP already knows the first query works with it.

If that suggestion doesn't resolve it, I would then take a look at the first two queries in post #1 specifically, the last two lines of each. they are different. and if we then look at the top line of the third query in post #1, we can see that his non-working query uses a command which is normally used as a function (in query 3) . I would amend the broken query by using this line

$row = mysql_fetch_array($result);

instead of

$result = result_to_array($result);

bazz

[surreal5335]

wow, thanks a lot for all the help.

Old P: Thanks a lot for the suggestions, in a weird way I feel kinda good when I have question that a senior coder cant figure out... makes me feel like less of a newb.

Bazz: The @ symbol suggestion you mentioned didnt change anything.

The only problem with your other suggestion I see is that if I remove
$result = result_to_array($result);

and replace it with:
$row = mysql_fetch_array($result);

Then I would no longer be looping through the different posts, or call a function that loops through the different posts, printing out each one. besides $row = mysql_fetch_array($result) is used in the
result_to_array() function, its been put inside the foreach loop.

I have a feeling that its not find_posts() that is the problem but the result_to_array() function that is. (which has been added into the bottom of the first post)

I maybe completely wrong on this

Thanks a lot for your help

[surreal5335]

well I spilled over my code for a couple hours and turned on php error reporting.

These are the set of notices I am getting:



Notice: Undefined variable: post in /home1/royalvil/public_html/post_ads/_post.php on line 7

Notice: Undefined variable: post in /home1/royalvil/public_html/post_ads/_post.php on line 11

Notice: Undefined variable: post in /home1/royalvil/public_html/post_ads/_post.php on line 15



currently the code for that page is as follows:



<div class="post">
<h2><?php echo $post['title']; ?></h2>

<span class="body-text">
<?php echo $post['body']; ?>

</span>
<span class="username">
<?php echo $post['username']; ?>

</span>
<span class="email">
<?php echo $post['email']; ?>

</span>

</div>


right now my code for calling the looping function is being assigned to $posts. I have tried many ways to make it work as $post so the code above will get the info from find_posts() properly. When ever I try to change it over I get this notice:


Notice: Undefined index: body in /home1/royalvil/public_html/post_ads/_post.php on line 6

Notice: Undefined index: username in /home1/royalvil/public_html/post_ads/_post.php on line 10

Notice: Undefined index: email in /home1/royalvil/public_html/post_ads/_post.php on line 14


the only place I can find reference to $posts in my code is here:


//$posts = find_posts();

foreach($posts as $post) {

echo '<h2>'.$post['title'].'</h2><br/>';
echo $post['body'].'<br/>';
echo $post['email'].'<br/>';
echo $post['name'].'<br/>';
}


keep in mind these code snipets are spread out over several pages. Now this may seem like the answer, but I have tried leaving them uncommented so they would active, but that made no difference.

Does anyone have any suggestins as to what I can do?

Thanks a lot for the help