PHP Code Not Returning Results from MYSQL Query

[CPGAmy]

This is my first time working with JOIN.

 

First let me display the code:
 

	$queryimg2 = "SELECT `serv_dur4`.`site`, `serv_dur4`.`service`, `serv_dur4`.`t1m3`, `site_n4m3`.`id`, `serv_typ3`.`id` 
		FROM `serv_dur4` 
		INNER JOIN `site_n4m3` ON (`serv_dur4`.`site`=`site_n4m3`.`id`)
		INNER JOIN `serv_typ3` ON (`serv_dur4`.`service`=`serv_typ3`.`id`) 
		ORDER BY `serv_typ3`.`nam3` ASC";
	$resultimg2 = mysql_query($queryimg2)or die( mysql_error() . "<HR>".$queryimg2);
	while($img2 = mysql_fetch_row($resultimg2)){
		// $hours = $img2['serv_dur4.t1m3'];
		echo "<tr><td><td>" . $img2['serv_dur4.t1m3'] . "";
	}

I have been spending hours today on Google researching the crap out of this thing and can't seem to get it working.

 

I'm wanting this to output a matrix / table. This table is meant to be a matrix to show how long a service typically takes to do depending on the job site and the type of service that is being done there. The best way I could pull this off was using JOIN (or in my specific case, INNER JOIN) to spit out rows of the results. But I have been running into massive bumps along the way.

 

My initial dream was this to look like a real table with the job sites lining up along the top horizontal row (pardon, I can't remember which is the X/Y axis) and the types of services line up on the far left vertical row with all of the cells filled with the time durations. I couldn't get that to work so now I'm working on having the results just spit out in a long vertical line. If there is some way to make it happen the way my original plan was, please do let me know.

 

Anyways, I have been fighting with this using every single trick Google has shown me (again, spending hours and hours searching, reading forums, reading tutorials, reading discussions, etc) but there doesn't seem to be a single case that matches mine for some reason.

 

The problem is the coding is not outputting anything, not even error messages. I've tried various ways to set up the SQL code, nothing changed. I tried various ways to get PHP to display the results, nothing changed. I'm using XAMPP on my local drive, if that has anything to do with it (I tried Googling if XAMPP has JOIN turned off [i know with email functions it does have that off] but couldn't find anything about that either).

 

Any clue as to what will get this thing to pump out the results?

[gizmola]

Take php out of the equation.

 

Does your query work from command line mysql or phpMyAdmin or whatever tool you have that lets you issue raw sql to the database?

[CPGAmy]

I have no idea how to do that - how do I test that out in phpmyadmin?

 

EDIT

Dumb question! I found it. =P

 

It gives me result there and are the results I'm looking for.

Edited May 6, 2014 by CPGAmy

[Ch0cu3r]

By pasting the query into the SQL field  (Login to PMA, select database and then click the SQL tab). PMA should then list the results of the query

[CPGAmy]

In case it was missed, I did the query and it gives me result there.

[Psycho]

From the manual

 

 

mysql_fetch_row:

 

Returns a numerical array that corresponds to the fetched row and moves the internal data pointer ahead.

 

When you have these types of problems, take a breath and determine what data you can inspect to determine what the problem may be. As Ch0cu3r proposed, checking the results of the query is a good first step. Once you verify that you could have inspected the HTML code and you would have seen multiple lines of the table rows with no content. The next logic steps would have been to inspect what the value of $img2 is using either print_r() or var_dump(). Either one would have made the problem perfectly evident.

Edited May 6, 2014 by Psycho

[gizmola]

As Psycho pointed out, your current code is trying to work with an associative array. No problem with that, just change mysql_fetch_row to mysql_fetch_assoc.

 

Of course it just has to be said, that using the mysql_ api is deprecated and has been removed from php entirely. It is only a matter of time before you upgrade php and your code no longer works. You should be using mysqli_ or PDO/mysql.

 

I'd recommend PDO.

[Jacques1]

It would actually have been enough to turn the error reporting on: Go to the php.ini and set display_errors to On and error_reporting to -1. 

 

Always make sure that the error reporting is on and up when you write code. PHP will warn you about many errors, including invalid indexes. But of course you'll miss this when you suppress the messages.

 

Yes, and switchting to PDO is definitely a good idea.

[CPGAmy]

The errors do come up when I screw up in other areas. For example, when I initially wrote the code in the beginning stages, instead of saying FROM I had accidentally wrote ROM. The page did its job and pitched a fit about it and when I corrected it, no more error message. So my error messages have been turned on and weren't being suppressed. Just thought I would throw that out there since it was brought up; nobody is psychic to be able to know my settings.

I tried mysql_fetch_assoc and that made no difference.

 

If I am understanding the mysqli_ direction, I changed all my queries to using mysqli_ instead of mysql_ and I am getting error messages. I switched them back to mysql_ and the error message are gone.

[gizmola]

If I am understanding the mysqli_ direction, I changed all my queries to using mysqli_ instead of mysql_ and I am getting error messages. I switched them back to mysql_ and the error message are gone.

Not really the point. Just changing everything from mysql_ to mysqli_ does not address the problem adequately. You actually have to learn the new api.As for the code, try this and tell us what you get:

 

while($img2 = mysql_fetch_assoc($resultimg2)){
    var_dump($img2); die();
    // $hours = $img2['serv_dur4.t1m3'];
    echo "<tr><td><td>" . $img2['serv_dur4.t1m3'] . "";
}

This was suggested earlier --- you need to understand what the $img2 variable actually contains at this point.

[Jacques1]

nobody is psychic to be able to know my settings.

 

No, but I've been using PHP long enough to know that nonexistent array keys trigger a notice – that is, unless you're suppressing notices.

 

I think you confuse your own error handling (the die() stuff) with the internal error handling of PHP. Those are two entirely different things. Of course your own error messages do work. But you've forgotten to turn the internal errors on. That's the ones I'm talking about.

 

 

 

If I am understanding the mysqli_ direction, I changed all my queries to using mysqli_ instead of mysql_ and I am getting error messages. I switched them back to mysql_ and the error message are gone.

 

You can't just append an “i” to all mysql_* functions. That may seem to be the easy way out, but it doesn't work. The MySQLi extension is very different from the old MySQL extension, so you have to actually rewrite your code.

 

Actually, I strongly advice against MySQLi. As the previous posters have already pointed out, you should be using PDO. It's much more powerful and convenient.

 

 

 

I tried mysql_fetch_assoc and that made no difference.

 

You need to leave out the "<table>." part. The name of the columns is what comes after the dot.

Edited May 6, 2014 by Jacques1

[CPGAmy]

I'm sensing some tension that is starting to warm up here...So let me go ahead and explain something I probably should have cuz I really don't want folks to get the wrong impression and to get mad at me because of a simple misunderstanding. I have 0 understanding of what's going on so there needs to be some patience with me, guys. I am a novice at best, which I probably should have stated in my initial post to help everyone see where I am in the PHP/MYSQL world. So I apologize for not letting you know sooner - that might have helped.

 

@gizmola:

 

This shows up when I replace that section of code with your code:

array(4) { ["site"]=> string(1) "3" ["service"]=> string(1) "2" ["t1m3"]=> string(7) "4 hours" ["id"]=> string(1) "2" }

 

Circling back to my above statement, I have 0 understanding of what is going on here and am a novice. Should this be outputting just one entry? I have three [test] entries in that table that the [initial project] code was expected to show.

[gizmola]

So, I hope you understand what an associative array is. Your code is looking for a particular key in an array. It then outputs the html for a table row, but because the key does not exist, that html row is empty.

 

The var_dump is showing you the keys for column names that are coming back from the query:

 

 

array(4) { ["site"]=> string(1) "3" ["service"]=> string(1) "2" ["t1m3"]=> string(7) "4 hours" ["id"]=> string(1) "2" }

The keys/column names available are: site, service, t1m3 and id.

 

Now just fix the key name in your echo statement:

 

 

while($img2 = mysql_fetch_assoc($resultimg2)){
    // $hours = $img2['serv_dur4.t1m3'];
    echo "<tr><td><td>" . $img2['t1m3'] . "";
}

Watch the magic happen.

 

We can only hope that you picked up how var_dump can help you debug. Hopefully you also understand how arrays, and in particular arrays with associative keys work.

[CPGAmy]

Very interesting! So what exactly is mysql_fetch_assoc doing that mysql_fetch_row isn't? I tried to read their pages on php.net but that website is pretty frustrating and I can't understand what they are saying when they discuss these functions (their text may as well be written in Greek). Only thing I could grasp from the website is that it is deprecated (mysql_fetch_row is as well). I have used mysql_fetch_row for years and it has given me the results that I was looking for and it is working in other pages related to this project. Does it just not "get" JOIN whereas mysql_fetch_assoc does? Or is it something else?

[Jacques1]

Just read the function description and skip the user comments:

 

 

mysql_fetch_assoc()

 

Returns an associative array that corresponds to the fetched row [...]

 

mysql_fetch_row()

 

Returns a numerical array that corresponds to the fetched row [...]

 

That looks pretty straightforward to me: One returns a numerical array (with the keys 0, 1, 2, ...), the other one returns an associative array (with the column names as the keys).

 

If you want to access the values by the column names, you need an associative array. If you want to access the values by the column indexes (which should be rare), you need a numerical array.

 

I don't know what you mean by “it is working on other pages”, but those two functions always work the same.

[CPGAmy]

I don't know what you mean by “it is working on other pages”, but those two functions always work the same.

 

"Working on other pages" as in other php files I made use mysql_fetch_row() and they give me results. Would it help if I gave an example? I know I'm terrible at explaining things.

[Jacques1]

I'm sure it gives you results. The thing is that it doesn't give you an associative array.

 

If you're saying that you've managed to make mysql_fetch_row() return an associative array, that would indeed be interesting.

[Psycho]

To follow up on Jacques1's response: Yes, fetch_row will work but, as the manual states, it will return a numerically based index. So, you would reference the records using numbers based on the order of the fields in the select clause (0 for the 1st field, 1 for the second, etc.). Whereas, fetch_assoc returns an array using the names of the fields (or aliases if you use them) as the index of the array. It has nothing to do with whether you used a JOIN in your query or not. So, if the other uses of it are working for you, it is because you are referencing the fields using numerically based indexes. I encourage you to always use associative arrays when possible. It makes your code easier to read and problems can occur if you use numerical indexes and make any changes in table structures or in the fields returned in the query.

 

I agree, though, that the manual was pretty clear on the explanation. And, it kinda irks me when people come on here asking for help, we point to the entry in the manual that explains their problem, then they state that it's too "complicated". If the PHP manual is too complicated (which is one of the best technical manuals I've ever used) for someone then maybe programming isn't what they should be doing. I'm not meaning that as a derogatory comment to you. Instead, I encourage you to pick a single function that you already know pretty well and read the entire entry in the manual for that function. Figure out what the different sections on the page are for and how they are laid out. Once you understand how the manual is constructed it becomes easier to read an entry for something that is new to you.

 

Anyway, I'll be generous and provide some examples on three flavors of the _fetch functions. Assuming you run this query:

$query = "SELECT name, address, phone
          FROM people
          WHERE id = 22";
$result = mysqli_query($link, $query);

Three of the _fetch functions would create arrays as follows

 

fetch_row (numerically based indexes)

$row = mysqli_fetch_row($result)

array
(
    0 => David Smith
    1 => 123 main Street
    2 => 213-456-7890
)

i.e. to reference the name you would use $row[0]

 

fetch_assoc (associative based indexes)

$row = mysqli_fetch_assoc($result)

array
(
    name => David Smith
    address => 123 main Street
    phone => 213-456-7890
)

i.e. to reference the name you would use $row['name']

 

fetch_array (return BOTH a numerical and associative based array)

$row = mysqli_fetch_array($result)

array
(

    0 => David Smith
    1 => 123 main Street
    2 => 213-456-7890

    name => David Smith
    address => 123 main Street
    phone => 213-456-7890
)

i.e. to reference the name you could use $row[0]  or $row['name']. That is the default behavior for this function. But, it takes an optional second parameter to determine whether the results are returned as BOTH numerical and associative or only one or the other.

 

 

 

There is also the fetch_object which returns an object that has properties named for each field.

$row = mysqli_fetch_object($result)

To reference the name you would use $row->name

Edited May 7, 2014 by Psycho

[CPGAmy]

See, your explaination makes so much more sense. Now I can see the difference between numerical array and associative array, which I now can understand why my code wasn't working in the way I expected it to. The examples provided really showed what associative and numerical arrays were.

 

The manual is obviously written for folks who already have a lot of the knowledge and training and that the manual seems to be more in the direction of a "reminder" reference. I would not have gotten the information from the manual at all that Psycho laid out. Repeating terms that I don't know what they mean, after I stated I didn't know what they were, in attempt to explain things does not make it any clearer or easier to understand.

 

I wish there was some formal class locally I could go to (and don't cost thousands of dollars) because just to be able to read the texts that are available on PHP, it is obvious you need a lot of background training in terminology and other basics before you can even understand what is being said. Touching on that type of subject seems to be in less supply than the other texts far as online searches go (versus HTML which seems to have a lot of texts written in laymans terms). It is certainly important and this discussion has definitely shown me there is a lot of background knowledge that should have been picked up along the way of me making it to where I am now.

 

A lot of folks who come to these forums looking for help don't have the knowledge that you folks do. It would seem you have forgotten what it was like to see PHP terms for the first time ever. If dealing with newbies to programming is so frustrating for you that you come to a newcomer to the forums with complaints about newbie-programmers, maybe you should reconsider spending your time in helping newbies. Maybe you should save your mentorship for the folks who are more experienced and just not participate with the newbies?

 

Or perhaps all of that is incorrect and I am the ignorant one. Is PHPFreaks forums limited to only the experienced programmers and not the place for novice programmers? Should I be going somewhere else that is more appropriate for my skill level?