/    Sign up×
Community /Pin to ProfileBookmark

php mySQL query

Copy linkTweet thisAlerts:
Jul 22.2010

I want to do the simpliest thing and its not working.

I have one table called serials
It contains all serial numbers of many products
Fields:

serialID (primary key)
ownerID (interger)
serialNum (varChar)

Other table is called serialowners – and contains owners of serial numbers
Fields:
ownerID (primary key – interger)
serialNum (varChar -I want to remove this field in this table because it is duplicate data)
fName
lName
etc.

One owner may have multiple serialNum but there may be serial numbers entered where an owner has not registered so there may or may not be a matching row in the serialowners table.

I want to display ALL serial numbers and if they have an owner, that info will display and if not, the serial number will still display. Should be such a simple thing with a LEFT JOIN

Here is my query:

[CODE]
$query = mysql_query(“SELECT * FROM serials
LEFT JOIN serialowners
USING (ownerID)
ORDER BY serialNum
“);

[/CODE]

When using this query, I get an error and nothing is returned
However, if I change the query to the following (the USING line), it displays the data correctly and pulls all serialNum from the serials table

[CODE]
$query = mysql_query(“SELECT * FROM serials
LEFT JOIN serialowners
USING (serialNum)
ORDER BY serialNum
“);

[/CODE]

I want to remove the serialNum from the serialowners table because I want to tie the 2 tables together with ownerID which are integer values.

So why can’t I run the first query using the ownerID ? There is something going on with it being an integer because the varChar of serialNum works correctly.

I hope this makes sense.

Can someone help me please?

to post a comment
PHP

4 Comments(s) ↴

Copy linkTweet thisAlerts:
@NogDogJul 22.2010 — I don't know why it wouldn't work unless the two columns are not, in fact, identically named (in which case you would need to use an ON clause instead of USING). What is the exact error message (e.g. from mysql_error()), or is there even any MySQL error?
Copy linkTweet thisAlerts:
@waMPauthorJul 22.2010 — Here is my function

[CODE]


function getAllSerialData_serials() {

$SERIALS = array();

$query = mysql_query("SELECT * FROM serials
LEFT JOIN serialowners
USING (ownerID)
ORDER BY serialNum
");


$num_rows = mysql_num_rows($query);
if($num_rows > 0) {
while($ROW = mysql_fetch_array($query, MYSQL_ASSOC)) {
$SERIALS[] = $ROW;
}
}
return $SERIALS;

}[/CODE]

I get a warning

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:xampphtdocsSITESintrepidWebMgrmoddb_serials.php on line 23

AND it does not pull any results
Copy linkTweet thisAlerts:
@waMPauthorJul 22.2010 — This query got rid of the warning and display the serialowner data, but does not display the serialNum

[CODE]
 $query = mysql_query("SELECT * FROM serials
LEFT JOIN serialowners
ON serials.ownerID = serialowners.ownerID
ORDER BY serials.serialNum
");

[/CODE]


I guess I need to tell it all the fields instead of using * like the following, but how do I do the LEFT JOIN fields?? The following gives me serialNums but no serialowner data.

[CODE]

$query = mysql_query("SELECT serials.serialNum, serials.status FROM serials
LEFT JOIN serialowners
ON serials.ownerID = serialowners.ownerID
ORDER BY serials.serialNum
");


[/CODE]
Copy linkTweet thisAlerts:
@waMPauthorJul 22.2010 — I got it.....................

[CODE]
 $query = mysql_query("SELECT serials.*, serialowners.* FROM serials
LEFT JOIN serialowners
USING (ownerID)
ORDER BY serials.serialNum
");

[/CODE]

Thanks for your help NogDog. You have helped me before on other issues and I truely appreciate your knowledge and everything you do!!!!
×

Success!

Help @waMP spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.24,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...