Jump to content

PHP Code Not Returning Results from MYSQL Query


CPGAmy
Go to solution Solved by gizmola,

Recommended Posts

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?

Link to comment
Share on other sites

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 by CPGAmy
Link to comment
Share on other sites

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 by Psycho
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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 by Jacques1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Solution

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Psycho
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.