|
|
Posted by Jerry Stuckle on 03/29/07 15:13
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
==================
[Back to original message]
|