[Resolved] [Resolved] MySQL questions JOIN & empty fields

chet23

I have two tables that I have been struggling all day with doing a JOIN on. At this point i think I've pulled out just about all the hair I have left.

Unable to figure out the correct syntax for doing a JOIN, I somehow managed to do two queries and get (almost) the results I want.

The code I am using follows the questions at the bottom of this post

Question 1:
I have the results printing out, which is great, except for the fact that a few cells of table data have no content. Instead of just omitting the missing content, I get an empty line returned.

Here's the output:

COMPANY #1

Woodland Hills
CA
91367
USA

COMPANY #2
12345 Anystreet. St.
Anytown
CA
93720
USA
555-555-5555
555-555-5555

As you can see, there's a missing line betwixt the 1st company name and the city because there is no street address for this company in the db. Likewise, there are no phone or fax #s, so there's two extra lines that are empty before the next company name appears. Company #2 is actually displayed correctly as all fields are present in the db.

How can I just skip over these emtpy fields??

Question 2:
There HAS to be a better way of returning these results than what I've put together. I'm sure a JOIN would fit the bill, but I can't figger it out for the life of me.

I'm most concerned with having my results output correctly. If I can get help with the first question I can live with that... If Someone can help me with both questions, I will forever be in your debt.

Here's the code I am using:

/* Define first query */
    $query = 'SELECT * FROM mailcode_ranges WHERE zip_low <= "'.$zipInput.'" AND zip_high >= "'.$zipInput.'"';
    $result = mysql_query($query) or die("Query failed: " . mysql_error());

/* both queries & output  */
    while($row = mysql_fetch_array($result)) { // put results into an array
        $ndc = $row["dist_id"];

        /* Define second query */
        $query2 = 'SELECT * FROM distributors WHERE "'.$ndc.'" = dist_id';
        $result2 = mysql_query($query2) or die("Query failed: " . mysql_error());

         while($row2 = mysql_fetch_array($result2)) { // put results into an array
               $ndc2 = $row2["company"];
               echo $row2[company] . "<br>";
               echo $row2[address] . "<br>";
               echo $row2[city] . "<br>";
               echo $row2[state] . "<br>";
               echo $row2[zip] . "<br>";
               echo $row2[country] . "<br>";
               echo $row2[phone] . "<br>";
               echo $row2[fax] . "<br><br>";

         }

}

Thank you, thank you, thank you, whoever you are!

-Chet23

ahundiak

To skip blank data do something like:
if ($row['address']) echo $row['address'] . "<br />\n";
To do a regular join you want something like
SELECT * FROM mailcode,distributers
WHERE zip_low <= "'.$zipInput.'" AND zip_high >= "'.$zipInput."'
AND mailcode.dist_id = distrubuters.dist_id

chet23

ahundiak, you're my hero!!! 😃

I have literally been wrestling with that stupid query all day.. I can't believe how close I actually was to making it work on my own.. Where you have the 2nd AND, i was using ON. Otherwise i had the same query that you just gave me. So near and yet so far!

And thanks for the 'if' statements.. They work like a charm.

I really appreciate your help on this.

While I might still have your attention, I would like to ask you two more small questions:

What's with writing html tags with /> ?? ex: <br /> Unless I'm mistaken I only see that in closing tags. I know there must be a good reason for it, but haven't been able to figure out why. Should that syntax be used for both opening and closing tags?? ex: <div /></div />

2.
I'm using substr() to truncate strings.
If the string starts with a #, i need to cut it down to three: substr($number, 0, 3). If the string starts with a letter, I only need the first char so I'm doing: substr($letter, 0, 1)

Is there a simple way of determining if a string starts with a number vs. a letter?
ex: $number = 12345;
$letter = a1234;

Again, a thousand thanks.

-Chet23

ahundiak

<br /> is actually shorthand for <br></br>
In html, using <br> by itself is perfectly legal. However, in xhtml (and xml in general) all opened tags need to be closed. Hence the use of the />. Note that <br/> (no space after br) is also legal xhtml but many older browsers fail on it. Hence the space.

You would only use /> when the tag has no content. The input tag is probably the other most common xhtml tag where /> is used. You always have conent with div tags so you would not use <div />

The w3c.org site has lot's of info on this stuff.

chet23

Thank you. I knew there had to be a good reason for coding that way. I haven't looked too closely at xml yet - still struggling a bit with the php/mysql thing.

As for my question about determining letters vs. numerals in strings, I came up with this:

<?php

$zipInput = "abc123456789" ;
$zipInput = substr($zipInput, 0, 3);
$zipTemp = substr($zipInput, 0, 1);

if (is_numeric($zipTemp)) {
    echo "'$zipInput'" . "is a number";
} else {
    $zipInput = $zipTemp;
    echo "'$zipInput'" . "is a letter";
}

?>

It seems to do the trick.

One last thing.. What's the most efficient way to know (and echo an appropriate message) when a db query turns up no results? For example with the query you helped me with above. I can get found results to print out one after another, but how exactly do you return "no records found?"

Don't sweat answering this question if you've helped me enough. Just figgered I'd ask - this is it, i swear!! You HAVE helped me tremendously, and I really appreciate it. When my pup pops, you've got pick of the litter!

-Chet23

chet23

Never mind. I think I've got it..

I'm gonna use something to the effect of:

if (!$result) {
   echo( "<p>You DO NOT have any results</p>" );
   exit();
}

Thanks again for everything.

-Chet23

ahundiak

No problem. Just be aware that $result will always have a value unless the query itself fails (usually because of syntax errors). You might try checking the number of rows returned.

chet23

Thanks for the heads up.

Pray tell, how would I apply that to this???

    while($row = mysql_fetch_array($result)) { // put results into an array
        $ndc = $row[""];
        if ($row['company']) echo $row['company'] . "<br />\n";
        if ($row['address']) echo $row['address'] . "<br />\n";
        if ($row['city']) echo $row['city'] . "<br />\n";
        if ($row['state']) echo $row['state'] . "<br />\n";
        if ($row['zip']) echo $row['zip'] . "<br />\n";
        if ($row['country']) echo $row['country'] . "<br />\n";
        if ($row['phone']) echo $row['phone'] . "<br />\n";
        if ($row['fax']) echo $row['fax'] . "<br />\n";
        //echo "<br><br>";
    }

chet23

I added this which works:

$number=mysql_numrows($result);
if ($number == 0) echo "No dice.";

This is amounts to an additional query to the server. Is the a more elegant, and less server-intensive way. of doing this?

-Chet23

ahundiak

For my_sql, $number=mysql_numrows($result);
does not actually go back out and query the server. The routine can tell how many records there are by examining the data structure pointed at by $result. Not all databases work this way.

If you really want to avoid it then:

$haveRows = false;
while($row = mysql_fetch_array($result)) {
    $haveRows = true;
}
if (!$haveRows) {
  echo 'No rows';
}