INNER JOIN???

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

I assumed because of the way your code is set out that $maklumat was an array of data from the database, basically it isn't, you need to loop through this resource to get any data out. Which is confusing because then you should have had errors each time you tried to access things like $maklumat["idMajor"].

So what do you get if you do:

Code: Select all

while ($row = mysql_fetch_assoc($maklumat)) {
    echo '<pre>';
    print_r($row);
    echo '</pre>';
}
Mac
User avatar
apek
Forum Commoner
Posts: 96
Joined: Tue Jan 06, 2004 11:19 pm
Location: Holiday Plaza Johor Bahru

......

Post by apek »

err..where should i put that??? :roll:
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Where you put the last bit of code I gave you.

Mac
User avatar
apek
Forum Commoner
Posts: 96
Joined: Tue Jan 06, 2004 11:19 pm
Location: Holiday Plaza Johor Bahru

.......

Post by apek »

err...i got nothing...
it just like usual..
no error appears...and i still cant retrieve from the $maklumat...
hmmm..how does the UPDATE can be done while the $maklumat query failed...
argghhhh..!!!
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

The query didn't fail "resource id #2" is a database result resource - a bunch of table rows (usually...).

A query can "succeed" but not return any rows. Errors tell you if the query isn't legal but don't tell you what it did or did not do.

Immediately after the $maklamut line, write this:

Code: Select all

<?php
die('num rows is: ' . mysql_num_rows($maklamut) . '<br />'); #DEB
?>
If there are no rows in the query you'll need to look at your db query string. I'd suggest messing about in something like phpMyAdmin then come back to the script once you've got the correct sql.

PS: I don't like the display_errors test in the custom query function. I can see why it's there, but it complicates matters when you try to debug - as you can see from earlier posts.

I'd prefer something like this:

Code: Select all

<?php
function mysqlQuery2($mysql, $line, $file) 
{
    $query = mysql_query($mysql) or die('sql error: ' . mysql_errno() . ' - ' . mysql_error() . '<br/>' . $line . ' | ' . $file . '<br />' . $mysql . '<br />');
    return $query;
}
?>
You can toggle error display on and off by editing the function def.
Last edited by McGruff on Tue Aug 09, 2005 11:46 pm, edited 1 time in total.
User avatar
apek
Forum Commoner
Posts: 96
Joined: Tue Jan 06, 2004 11:19 pm
Location: Holiday Plaza Johor Bahru

.........

Post by apek »

i get this...

Code: Select all

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in g:\programming\phpdev\www\mmis\system\semakaduan1.php on line 35
num rows is:
and the line 35 is the code u just gave me...
so...how?
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

That's odd.

A few posts back, twig asked you to print_r($maklamut) - and that returned a resource id, ie a valid db result resource.

However, when you tried to run the mysql_num_rows function on $maklamut (I assume at the same point in the script, ie immediately after $maklamut has been declared?) it says that it's NOT a valid result resource.

I'm not sure what's going on right now. We'll get there eventually though.

At this point (unless twig comes in with the answer..) it might help if we try a much simplified test script containing just:

(1) the UPDATE query

(2) the $maklamut SELECT query

I also think it might help to ditch your custom query function, at least for the moment.

Although in some ways it's not a perfect moment to introduce new ideas, I think you should wrap the code up in functions. Each function should do just one thing.

This makes it much clearer what's going on and easier to test the individual parts when you have to track down the source of script problems. Starting to shift vars out of the dreaded global scope is also a Good Thing for easier debugging.

Code: Select all

<?php
// replaces your query() function
function mysqlQuery2($mysql, $line, $file)
{
    $query = mysql_query($mysql) 
    or die( 'sql error: ' . mysql_errno() . ' - ' . mysql_error() . '<br/>'
            . $line . ' | ' . $file . '<br />'
            . $mysql . '<br />'
           );
   
    return $query;
} 

function theUpdate($id) 
{
    $mysql = "UPDATE aduankerosakan SET status='Diproses' WHERE idAduan = '" . $id . "'";
    mysqlQuery2($mysql, __LINE__, __FILE__);
    echo 'UPDATE: affected rows = ' . mysql_affected_rows() . '<br />'; #DEB    
}

function theSelect($id) 
{
    $maklumat_query  = "SELECT [..name the cols..] FROM aduankerosakan 
                        INNER JOIN tpartminor USING(idMajor)
                        WHERE aduankerosakan.idAduan ='" . $id . "'";

    $result = mysqlQuery2($maklumat_query, __LINE__, __FILE__); 
    echo '$maklamut num rows is: ' . mysql_num_rows($result) . '<br />'; #DEB

    return $result;
}

// set a valid value - check the table first
$id = ??;

// now see if it works..
theUpdate($id);
theSelect($id);


?>
PS: I don't have your db to test that on so I hope I didn't leave any embarassing parse errors in :?
Last edited by McGruff on Tue Aug 09, 2005 11:40 pm, edited 1 time in total.
quesadilla5
Forum Newbie
Posts: 4
Joined: Sat Jan 17, 2004 12:29 am

Post by quesadilla5 »

Try concatenation to fix the problem with $_POST, eg:
PHP:

<?php
"UPDATE aduankerosakan SET status = '" . $status . "' WHERE idAduan = '" . $_POST['id'] . "' "
?>



To get the error, you need to test for $result == false in your custom query function, ie:

PHP:

<?php
if ($result == false && ini_get('display_errors') == 1)
?>
I'm sorry but I have to point this out...

empty(), a language construct, checks for false, null, empty string, zero and string 0 values, so... empty($value) is just like saying if $value==false

Second, concatenation, will produce identical output to the curly brace syntax used. Curly syntax in a nutshell just tells PHP that, when delimiting a variable within a string with curly braces to drop out of normal string parsing mode and treat that object like a varible, which is handy for including nested multidimensional arrays and objects within a string.

References:
http://www.php.net/empty
http://www.php.net/manual/en/language.types.string.php

Just FYI guys.

Apek,
First a bit of advice,

You include a file in each of your scripts...

<?php
include("../inc/config.php");
?>

Why don't you place the query() function in that file... then it is always available and you don't have to cut and paste it.

Second:

Your inclusion of this form tag:

<form name="myform">

Is without both an action and method attribute.

on semakAduan.php AND in semakAduan1.php

action tells the browser where to send the data from the form, and method tells the browser how to send the data, as a query string appended to a url (the GET method) or invisibly behind the scenes (the POST method).

For your application I would reccomend using the POST method.

So in: semakAduan.php

modify the form tag to say the following:

<form name="myform" action='semakAduan1.php' method='post'>

Then in 'semakAduan1.php' your may access data from 'semakAduan.php' in the PHP $_POST superglobal array.

Ok, my next question is where is the HTML input field to post the data to the second page? Or the query string? You have no code that produces a way to transfer this value to the other page.

$_POST["id"]

Would reference an HTML input field by the same name...

Perhaps a hidden field?
<input type='hidden' name='id' value='' />

You would place the hidden field between the <form> tags in 'semakAduan.php', and place the needed value in the value='' attribute.

If I understand what I'm seeing properly that outght to be of help.

Also, <? ?> known as short tags are not a good habit to get into. These can be deactivated in php.ini, and are not XML compliant. For the sake of portability and just plain ole good programming practice, use <?php ?>, which is XML compliant and gauranteed portable.

: )
Rich
User avatar
apek
Forum Commoner
Posts: 96
Joined: Tue Jan 06, 2004 11:19 pm
Location: Holiday Plaza Johor Bahru

Post by apek »

// set a valid value according to the col type
$id = ??;
what do u mean??
quesadilla5
Forum Newbie
Posts: 4
Joined: Sat Jan 17, 2004 12:29 am

Post by quesadilla5 »

// replaces your query() function
function mysqlQuery2($mysql, $line, $file)
{
$query = mysql_query($mysql)
or die( 'sql error: ' . mysql_errno() . ' - ' . mysql_error() . '<br/>'
. $line . ' | ' . $file . '<br />'
. $mysql . '<br />'
);

return $query;
}
I wouldn't recommend that method for a couple of reasons,

Database wrapper functions should be ambiguous, using the above you are limiting usage to mysql only. Whereas with a non-specific set of names you may also incorporate functionality to use the custom function with another database.

Though not to be all bad, your are right about designing applications with reusability in mind!

Code: Select all

<?php

define("USE_DB", "mysql");

query($sql, $line = null, $file = null, $link = null)
{	

	// Attempt to import the resource handler from $GLOBALS['link'];

	if (is_null($link))

		$link = $GLOBALS['link'];


	switch (USE_DB)
	{
		case 'mysql':
			$result = mysql_query($sql, $link);
			
			if (empty($result))

				db_error($link, $line, $file);		

			break;

		case 'pgsql':

			$result = pg_query($link, $sql);

			if (empty($result))

				db_error($link, $line, $file);
			break;


	}
}

function db_error($link, $line = null, $file = null)
{
	switch (USE_DB)
	{
		case 'mysql':
			echo mysql_errno($link).': ';
			echo mysql_error($link)."<br />\n";

			break;

		case 'pgsql':
			echo pg_last_error($link)."<br />\n";

			break;
	}

	if (!is_null($line))
		echo "@ line: $line ";
	if (!is_null($file))
		echo "in file: $file<br />\n";

}
?>
This kind of thinking is reusable in future projects and adaptable to your client's needs! And brings a greater portability to your database..

: )
Rich
Last edited by quesadilla5 on Sat Jan 17, 2004 12:59 am, edited 2 times in total.
User avatar
apek
Forum Commoner
Posts: 96
Joined: Tue Jan 06, 2004 11:19 pm
Location: Holiday Plaza Johor Bahru

..........

Post by apek »

o please...
pls back to my problem please... :(
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

apek wrote:
// set a valid value according to the col type
$id = ??;
what do u mean??
Sorry - I edited my original post. What I meant to say was make sure you set a test value which does actually exist in the table.

Let me know how you get on.

quesadilla5 you're getting a little bit OT. If you want to discuss the code I posted could you start a new topic? I'm not sure all your comments are valid but this isn't the place to deal with them.
User avatar
apek
Forum Commoner
Posts: 96
Joined: Tue Jan 06, 2004 11:19 pm
Location: Holiday Plaza Johor Bahru

.........

Post by apek »

both of you are really good and really helping...
thanx a lot....!!!!!!!!!!
but for the time being i'll answer mcgruff first...
i did what u said...
i set a test value which does actually exist in the table.
i get this:

Code: Select all

affected rows = 1
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in g:\programming\phpdev\www\mmis\system\semakaduan1.php on line 55
num rows is:
so mcgruff..whats that?

and for quesadilla5,actually i tried the action and method like usa id,but its still the same....but i'll try again,maybe it'll work this time...

p/s:actually this two page of code is from asp,i translate it to php according to my needs,and in the original asp,theres no form action or method,thats why theres no such thing in my form.....but the asp can run smoothly...
but in the asp semakAduan.asp form,at the top of the form theres a line like this:

Code: Select all

<% Response.Buffer=True %>
quesadilla5
Forum Newbie
Posts: 4
Joined: Sat Jan 17, 2004 12:29 am

Post by quesadilla5 »

Ok one more question Apek, in addition to commentary already supplied,

$maklumat = query("SELECT * FROM aduankerosakan INNER JOIN tpartminor ON aduankerosakan.idMajor = tpartminor.idMajor WHERE aduankerosakan.idAduan ='" . $_GET['id'] . "'", __LINE__, __FILE__);

You use the above in one of your queries, but in an earlier problem back on wrox in http://p2p.wrox.com/topic.asp?TOPIC_ID=8284 you use the following:

$query2 = "Select * from ".$DBprefix." tPartMinor where substring(idMinor,0)='$front'";

What is $DBprefix and are you supposed to be using that here?

OH AND ALSO,
You modified my query function to *not* take the $link argument, how are you referencing the MYSQL database link resource handler???

I recommend reading this topic on Wrox again, within a user-defined function specifing that as an argument becomes necessary to avoid bugs!

Read my explaination of how the query() function works again.
http://p2p.wrox.com/topic.asp?TOPIC_ID=8493
Without the $link either being imported via a superglobal or constant or passed as an argument explicitly your queries *may* fail!

One more error in semakAduan1.php that needs pointed out:

You make the query:

$maklumat = query("SELECT * FROM aduankerosakan INNER JOIN tpartminor ON aduankerosakan.idMajor = tpartminor.idMajor WHERE aduankerosakan.idAduan ='" . $_GET['id'] . "'", __LINE__, __FILE__);

But DON'T fetch the array before trying to output the results:

As in <?=$maklumat["tarikhAduan"]?>

Modify the query call to read:

$result = query("SELECT * FROM aduankerosakan INNER JOIN tpartminor ON aduankerosakan.idMajor = tpartminor.idMajor WHERE aduankerosakan.idAduan ='" . $_GET['id'] . "'", __LINE__, __FILE__);

$maklumat = mysql_fetch_array($result, MYSQL_ASSOC);

In your code $maklumat is the result set identifier, it contains information about what was matched in your query, but does not contain the data itself. You ALWAYS have to call on a function to fetch the results specifically.

: )
Rich
quesadilla5
Forum Newbie
Posts: 4
Joined: Sat Jan 17, 2004 12:29 am

Post by quesadilla5 »

quesadilla5 you're getting a little bit OT. If you want to discuss the code I posted could you start a new topic? I'm not sure all your comments are valid but this isn't the place to deal with them.
Hey man, nothing personal. I'm just pointing out the information. Don't take it personally. Apek requested my assistance specifically.

What does OT mean?

And if you're wrong I'm going to point it out. I'm just trying to help. Opening another topic isn't really called for if you gave misinformation in this topic. Which again, isn't against you. The language is what it is.

But Apek posted his problem in Wrox forums which I frequent and I thought since most of his information was posted here that here would be the better place to present the solution.

: )
Rich
Post Reply