Professional Web Applications Themes

Having trouble with left join - MySQL

On Mar 28, 5:38 am, Jerry Stuckle <net> wrote: [/ref] > [/ref] > [/ref] > [/ref] >[/ref] 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)  > > (Top ...

  1. #1

    Default Re: Having trouble with left join

    On Mar 28, 5:38 am, Jerry Stuckle <net> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    >[/ref]
    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) 
    >
    > (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.
    > net
    > ==================[/ref]

    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.

    chadlupkes Guest

  2. #2

    Default Re: Having trouble with left join

    chadlupkes wrote:
     

    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!
    Toby Guest

  3. #3

    Default Re: Having trouble with left join

    chadlupkes wrote: [/ref]
    > 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) 
    >>
    >> (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.
    >> net
    >> ==================[/ref]
    >
    > 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.
    >[/ref]

    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.
    net
    ==================
    Jerry Guest

  4. #4

    Default Re: Having trouble with left join

    On Mar 29, 8:13 am, Jerry Stuckle <net> wrote: 
    > > Here are the tables:[/ref]




    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]




    >
    > 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.
    > net
    > ==================[/ref]

    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

    chadlupkes Guest

Similar Threads

  1. Is left-join faster then inner join?
    By howachen@gmail.com in forum MySQL
    Replies: 5
    Last Post: March 5th, 07:11 AM
  2. Sql Left Join Help
    By Marc.Farrow in forum MySQL
    Replies: 8
    Last Post: October 9th, 06:00 PM
  3. Replies: 2
    Last Post: September 18th, 09:59 PM
  4. Rewrite Query using Inner Join, Left Join e.t.c
    By Vishal in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 23rd, 08:02 AM
  5. Select Left Join AND Right Join
    By David in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 15th, 03:42 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139