Re: Having trouble with left join
This is a discussion on Re: Having trouble with left join within the MySQL forums, part of the Database Server Software category; --> On Mar 28, 5:38 am, Jerry Stuckle <jstuck...@attglobal.net> wrote: > chadlupkes wrote: > > On Mar 27, 8:24 am, ...
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-28-2008, 09:30 AM
| ||||
| ||||
 Re: Having trouble with left join On Mar 28, 5:38 am, Jerry Stuckle <jstuck...@attglobal.net> wrote: > chadlupkes wrote: > > On Mar 27, 8:24 am, Toby A Inkster <usenet200...@tobyinkster.co.uk> > > wrote: > >> chadlupkes wrote: > >>> I'm getting NULLs where there shouldn't be. > >> To determine that, we'd need to see some of the data. > > >>> king_precinct > >> You've not given us a schema for this table. > > >> -- > >> Toby A Inkster BSc (Hons) ARCS > >> Contact Me ~http://tobyinkster.co.uk/contact > >> Geek of ~ HTML/SQL/Perl/PHP/Python*/Apache/Linux > > >> * = I'm getting there! > Here are the tables: king_precinct Field Type Null Key Default Extra id smallint(6) PRI NULL auto_increment precinct_number int(11) 0 precinct_name varchar(20) MUL countydist smallint(6) 0 congdist smallint(6) 0 legdist smallint(6) 0 "precinct_name" is the name of the precinct, not the PCO. That's in the next table. pcolist Field Type Null Key Default Extra id smallint(6) PRI NULL auto_increment precinct int(11) 0 pctname varchar(15) area varchar(4) legdist smallint(6) 0 countydist smallint(6) 0 congdist smallint(6) 0 name varchar(50) email varchar(50) MUL private tinyint(1) 0 type varchar(5) > > > > And since this is a PHP forum, which Jerry reminded me of, here is the > > code I'm trying to get to work: > > > > echo('<html> > > <head> > > <title>Precincts in King County</title> > > </head> > > <body> > > <table border="1"> > > <tr> > > <th>Precinct Number</th> > > <th>Precinct Name</th> > > <th>County Council District</th> > > <th>Congressional District</th> > > <th>Legislative District</th> > > <th>PCO Name</th> > > <th>PCO Type</th> > > </tr> > > '); > > $link_id = db_connect('wa46dems_data'); > > $result = mysql_query("SELECT king_precinct.precinct_number AS precinct_number, king_precinct.precinct_name AS precinct_name, king_precinct.legdist AS legdist, king_precinct.countydist AS countydist, king_precinct.congdist AS congdist, pcolist.name AS name, pcolist.type AS type > > FROM king_precinct LEFT JOIN pcolist > > ON king_precinct.precinct_number = pcolist.precinct > > ORDER BY precinct_name", $link_id); > > while($pct_data = mysql_fetch_array($result)) { > > $precinct_number = $pct_data["precinct_number"]; > > $precinct_name = $pct_data["precinct_name"]; > > $precinct_cc = $pct_data["countydist"]; > > $precinct_cd = $pct_data["congdist"]; > > $precinct_ld = $pct_data["legdist"]; > > $precinct_pco_name = $query_data["name"]; > > $precinct_pco_email = $query_data["email"]; > > $precinct_pco_private = $query_data["private"]; > > $precinct_pco_type = $query_data["type"]; > > echo(' > > <tr> > > <td>'.$precinct_number.'</td> > > <td><a href="precinctmap.php?pct='.$precinct_number.'">'. > > $precinct_name.'</a></td> > > <td>'.$precinct_cc.'</td> > > <td>'.$precinct_cd.'</td> > > <td>'.$precinct_ld.'</td> > > <td>'.$precinct_pco_name.'</td> > > <td>'.$precinct_pco_type.'</td> > > </tr>'); > > } > > echo('</table>'); > > > > (Top posting fixed) > > PHP isn't going to affect whether your SQL code returns null values or > not. If a particular row contains a null, you'll get a null back from > your sql. And since you're doing a left join, if there is no matching > row in pcolist you will get nulls for all values there. > > And you're still asking about the SQL, not PHP. You need to be asking > in a SQL newsgroup - you'll get much better help on your SQL question. > > And please don't top post. > > -- > ================== > Remove the "x" from my email address > Jerry Stuckle > JDS Computer Training Corp. > jstuck...@attglobal.net > ================== I think it's something to do with my PHP code, because when I run the SQL query within mysql, it works exactly how I want it to. I think I'm doing something wrong in the code, but I just can't see it. You can see the results that are coming out of the query here: http://46dems.com/kcdems/precinctinfo_html.php I'll copy the mysql group to see if anyone there has ideas that might work.      |
|
02-28-2008, 09:30 AM
| |||
| |||
| Re: Having trouble with left join chadlupkes wrote: > On Mar 28, 5:38 am, Jerry Stuckle <jstuck...@attglobal.net> wrote: >> chadlupkes wrote: >>> On Mar 27, 8:24 am, Toby A Inkster <usenet200...@tobyinkster.co.uk> >>> wrote: >>>> chadlupkes wrote: >>>>> I'm getting NULLs where there shouldn't be. >>>> To determine that, we'd need to see some of the data. >>>>> king_precinct >>>> You've not given us a schema for this table. >>>> -- >>>> Toby A Inkster BSc (Hons) ARCS >>>> Contact Me ~http://tobyinkster.co.uk/contact >>>> Geek of ~ HTML/SQL/Perl/PHP/Python*/Apache/Linux >>>> * = I'm getting there! > Here are the tables: > > king_precinct > > Field Type Null Key Default Extra > id smallint(6) PRI NULL auto_increment > precinct_number int(11) 0 > precinct_name varchar(20) MUL > countydist smallint(6) 0 > congdist smallint(6) 0 > legdist smallint(6) 0 > > "precinct_name" is the name of the precinct, not the PCO. That's in > the next table. > > pcolist > Field Type Null Key Default Extra > id smallint(6) PRI NULL auto_increment > precinct int(11) 0 > pctname varchar(15) > area varchar(4) > legdist smallint(6) 0 > countydist smallint(6) 0 > congdist smallint(6) 0 > name varchar(50) > email varchar(50) MUL > private tinyint(1) 0 > type varchar(5) >> > >> > And since this is a PHP forum, which Jerry reminded me of, here is the >> > code I'm trying to get to work: >> > >> > echo('<html> >> > <head> >> > <title>Precincts in King County</title> >> > </head> >> > <body> >> > <table border="1"> >> > <tr> >> > <th>Precinct Number</th> >> > <th>Precinct Name</th> >> > <th>County Council District</th> >> > <th>Congressional District</th> >> > <th>Legislative District</th> >> > <th>PCO Name</th> >> > <th>PCO Type</th> >> > </tr> >> > '); >> > $link_id = db_connect('wa46dems_data'); >> > $result = mysql_query("SELECT king_precinct.precinct_number AS precinct_number, king_precinct.precinct_name AS precinct_name, king_precinct.legdist AS legdist, king_precinct.countydist AS countydist, king_precinct.congdist AS congdist, pcolist.name AS name, pcolist.type AS type >> > FROM king_precinct LEFT JOIN pcolist >> > ON king_precinct.precinct_number = pcolist.precinct >> > ORDER BY precinct_name", $link_id); >> > while($pct_data = mysql_fetch_array($result)) { >> > $precinct_number = $pct_data["precinct_number"]; >> > $precinct_name = $pct_data["precinct_name"]; >> > $precinct_cc = $pct_data["countydist"]; >> > $precinct_cd = $pct_data["congdist"]; >> > $precinct_ld = $pct_data["legdist"]; >> > $precinct_pco_name = $query_data["name"]; >> > $precinct_pco_email = $query_data["email"]; >> > $precinct_pco_private = $query_data["private"]; >> > $precinct_pco_type = $query_data["type"]; >> > echo(' >> > <tr> >> > <td>'.$precinct_number.'</td> >> > <td><a href="precinctmap.php?pct='.$precinct_number.'">'. >> > $precinct_name.'</a></td> >> > <td>'.$precinct_cc.'</td> >> > <td>'.$precinct_cd.'</td> >> > <td>'.$precinct_ld.'</td> >> > <td>'.$precinct_pco_name.'</td> >> > <td>'.$precinct_pco_type.'</td> >> > </tr>'); >> > } >> > echo('</table>'); >> > >> >> (Top posting fixed) >> >> PHP isn't going to affect whether your SQL code returns null values or >> not. If a particular row contains a null, you'll get a null back from >> your sql. And since you're doing a left join, if there is no matching >> row in pcolist you will get nulls for all values there. >> >> And you're still asking about the SQL, not PHP. You need to be asking >> in a SQL newsgroup - you'll get much better help on your SQL question. >> >> And please don't top post. >> >> -- >> ================== >> Remove the "x" from my email address >> Jerry Stuckle >> JDS Computer Training Corp. >> jstuck...@attglobal.net >> ================== > > I think it's something to do with my PHP code, because when I run the > SQL query within mysql, it works exactly how I want it to. I think > I'm doing something wrong in the code, but I just can't see it. > > You can see the results that are coming out of the query here: > > http://46dems.com/kcdems/precinctinfo_html.php > > I'll copy the mysql group to see if anyone there has ideas that might > work. > Chad, I just took another look at this (actually in the comp.databases.mysql group) and your output. Your problem is right here: $precinct_pco_name = $query_data["name"]; $precinct_pco_email = $query_data["email"]; $precinct_pco_private = $query_data["private"]; $precinct_pco_type = $query_data["type"]; The problem is your result is in $pct_data from: while($pct_data = mysql_fetch_array($result)) A suggestion - any time you have problems like this, check your PHP error log (usually the webserver's error log) or add the following to the beginning of your script: error_reporting(E_ALL); ini_set("display_errors", "1"); This will display all errors, warnings, notices, etc. You should have gotten a notice on the above statements (missing index in the array - if it is even an array); So I was wrong and it was a PHP error - but something like when you have both mysql and php involved it never hurts to post to both groups. And actually seeing the output helped a lot. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstucklex@attglobal.net ================== |
|
02-28-2008, 09:30 AM
| |||
| |||
| Re: Having trouble with left join chadlupkes wrote: > I think it's something to do with my PHP code, because when I run the > SQL query within mysql, it works exactly how I want it to. Permissions? Are you running the queries under the same set of permissions in both cases? -- Toby A Inkster BSc (Hons) ARCS Contact Me ~ http://tobyinkster.co.uk/contact Geek of ~ HTML/SQL/Perl/PHP/Python*/Apache/Linux * = I'm getting there! |
|
02-28-2008, 09:31 AM
| ||||
| ||||
| Re: Having trouble with left join On Mar 29, 8:13 am, Jerry Stuckle <jstuck...@attglobal.net> wrote: > chadlupkes wrote: > > On Mar 28, 5:38 am, Jerry Stuckle <jstuck...@attglobal.net> wrote: > >> chadlupkes wrote: > >>> On Mar 27, 8:24 am, Toby A Inkster <usenet200...@tobyinkster.co.uk> > >>> wrote: > >>>> chadlupkes wrote: > >>>>> I'm getting NULLs where there shouldn't be. > >>>> To determine that, we'd need to see some of the data. > >>>>> king_precinct > >>>> You've not given us a schema for this table. > >>>> -- > >>>> Toby A Inkster BSc (Hons) ARCS > >>>> Contact Me ~http://tobyinkster.co.uk/contact > >>>> Geek of ~ HTML/SQL/Perl/PHP/Python*/Apache/Linux > >>>> * = I'm getting there! > > Here are the tables: > > > king_precinct > > > Field Type Null Key Default Extra > > id smallint(6) PRI NULL auto_increment > > precinct_number int(11) 0 > > precinct_name varchar(20) MUL > > countydist smallint(6) 0 > > congdist smallint(6) 0 > > legdist smallint(6) 0 > > > "precinct_name" is the name of the precinct, not the PCO. That's in > > the next table. > > > pcolist > > Field Type Null Key Default Extra > > id smallint(6) PRI NULL auto_increment > > precinct int(11) 0 > > pctname varchar(15) > > area varchar(4) > > legdist smallint(6) 0 > > countydist smallint(6) 0 > > congdist smallint(6) 0 > > name varchar(50) > > email varchar(50) MUL > > private tinyint(1) 0 > > type varchar(5) > > >> > And since this is a PHP forum, which Jerry reminded me of, here is the > >> > code I'm trying to get to work: > > >> > echo('<html> > >> > <head> > >> > <title>Precincts in King County</title> > >> > </head> > >> > <body> > >> > <table border="1"> > >> > <tr> > >> > <th>Precinct Number</th> > >> > <th>Precinct Name</th> > >> > <th>County Council District</th> > >> > <th>Congressional District</th> > >> > <th>Legislative District</th> > >> > <th>PCO Name</th> > >> > <th>PCO Type</th> > >> > </tr> > >> > '); > >> > $link_id = db_connect('wa46dems_data'); > >> > $result = mysql_query("SELECT king_precinct.precinct_number AS precinct_number, king_precinct.precinct_name AS precinct_name, king_precinct.legdist AS legdist, king_precinct.countydist AS countydist, king_precinct.congdist AS congdist, pcolist.name AS name, pcolist.type AS type > >> > FROM king_precinct LEFT JOIN pcolist > >> > ON king_precinct.precinct_number = pcolist.precinct > >> > ORDER BY precinct_name", $link_id); > >> > while($pct_data = mysql_fetch_array($result)) { > >> > $precinct_number = $pct_data["precinct_number"]; > >> > $precinct_name = $pct_data["precinct_name"]; > >> > $precinct_cc = $pct_data["countydist"]; > >> > $precinct_cd = $pct_data["congdist"]; > >> > $precinct_ld = $pct_data["legdist"]; > >> > $precinct_pco_name = $query_data["name"]; > >> > $precinct_pco_email = $query_data["email"]; > >> > $precinct_pco_private = $query_data["private"]; > >> > $precinct_pco_type = $query_data["type"]; > >> > echo(' > >> > <tr> > >> > <td>'.$precinct_number.'</td> > >> > <td><a href="precinctmap.php?pct='.$precinct_number.'">'. > >> > $precinct_name.'</a></td> > >> > <td>'.$precinct_cc.'</td> > >> > <td>'.$precinct_cd.'</td> > >> > <td>'.$precinct_ld.'</td> > >> > <td>'.$precinct_pco_name.'</td> > >> > <td>'.$precinct_pco_type.'</td> > >> > </tr>'); > >> > } > >> > echo('</table>'); > > >> (Top posting fixed) > > >> PHP isn't going to affect whether your SQL code returns null values or > >> not. If a particular row contains a null, you'll get a null back from > >> your sql. And since you're doing a left join, if there is no matching > >> row in pcolist you will get nulls for all values there. > > >> And you're still asking about the SQL, not PHP. You need to be asking > >> in a SQL newsgroup - you'll get much better help on your SQL question. > > >> And please don't top post. > > >> -- > >> ================== > >> Remove the "x" from my email address > >> Jerry Stuckle > >> JDS Computer Training Corp. > >> jstuck...@attglobal.net > >> ================== > > > I think it's something to do with my PHP code, because when I run the > > SQL query within mysql, it works exactly how I want it to. I think > > I'm doing something wrong in the code, but I just can't see it. > > > You can see the results that are coming out of the query here: > > >http://46dems.com/kcdems/precinctinfo_html.php > > > I'll copy the mysql group to see if anyone there has ideas that might > > work. > > Chad, > > I just took another look at this (actually in the comp.databases.mysql > group) and your output. Your problem is right here: > > $precinct_pco_name = $query_data["name"]; > $precinct_pco_email = $query_data["email"]; > $precinct_pco_private = $query_data["private"]; > $precinct_pco_type = $query_data["type"]; > > The problem is your result is in $pct_data from: > > while($pct_data = mysql_fetch_array($result)) > > A suggestion - any time you have problems like this, check your PHP > error log (usually the webserver's error log) or add the following to > the beginning of your script: > > error_reporting(E_ALL); > ini_set("display_errors", "1"); > > This will display all errors, warnings, notices, etc. You should have > gotten a notice on the above statements (missing index in the array - if > it is even an array); > > So I was wrong and it was a PHP error - but something like when you have > both mysql and php involved it never hurts to post to both groups. > > And actually seeing the output helped a lot. > > -- > ================== > Remove the "x" from my email address > Jerry Stuckle > JDS Computer Training Corp. > jstuck...@attglobal.net > ================== Isn't it true that 90% of errors like this just need an extra pair of eyes? Thanks, Jerry. I really appreciate your help. http://46dems.com/kcdems/precinctinfo_html.php Chad |
Linear Mode
