SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql_num_rows with two tables

    I have a script that uses the following mysql_num_rows function:

    PHP Code:
    $result mysql_query{"SELECT URL FROM gw_geog_gw WHERE URL = '$MyName'"$link};

    if (
    mysql_num_rows($result) === || empty($MyName))
    {
    // etc. 
    ...where gw_geog_gw is a table containing names/URL's of nations and states.

    I want this static page in my CMS to also display pages focusing on counties and school districts, so I have to add another table (which I'm still trying to figure out). If I name my second table gw_counties, how would I modify my script so that matching URL's from either table is accepted?

    In other words, it might look something like this:

    PHP Code:
    $result mysql_query{"SELECT URL FROM gw_geog_gw OR gw_counties WHERE URL = '$MyName'"$link}; 
    Thanks.

  2. #2
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT url FROM gw_geog_gw, gw_counties WHERE gw_geog_gw.url = '$MyName' OR gw_counties.url='$MyName'
    But what draws my attention is that you select URL, filtering by URL. So if you have it, why do you need to select it from db?
    Saul

  3. #3
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by php_daemon View Post
    Code:
    SELECT url FROM gw_geog_gw, gw_counties WHERE gw_geog_gw.url = '$MyName' OR gw_counties.url='$MyName'
    But what draws my attention is that you select URL, filtering by URL. So if you have it, why do you need to select it from db?
    Every entity in my database tables has a name, ID and URL, like this...

    NAME / ID / URL
    United States / usa / United_States
    California / ca / California
    Adams County / 003 / Adams_County

    If the value in the "URL" column matches the URL displayed in a visitor's browser - e.g. www.mysite.com/World/United_States - then the values for United States/usa are displayed.

    Thanks for the tip.

  4. #4
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It looks like your goal is to merely count how many rows you have where that URL appears, am I correct? As php_daemon points out, it seems unnecessary (at best) to SELECT url when you have it already in $MyName.

    If it's true that you're only after a count of rows, then do this instead:
    Code:
    SELECT COUNT(*)
    FROM gw_geog_gw, gw_counties
    WHERE gw_geog_gw.url = '$MyName'
        OR gw_counties.url = '$MyName'
    This is going to run a lot faster and be a lot more efficient, because it's only going to return a single row instead of every single row matching that condition. You'd then extract that result (using e.g. mysql_result) and use that in your conditional.
    PHP questions? RTFM
    MySQL questions? RTFM

  5. #5
    An average geek earl-grey's Avatar
    Join Date
    Mar 2005
    Location
    Ukraine
    Posts
    1,403
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't think that cross join is what the post author is after. geosite, are the tables linked somehow?

  6. #6
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by earl-grey View Post
    I don't think that cross join is what the post author is after. geosite, are the tables linked somehow?
    No, the tables aren't linked. I'm simply appending a table with counties and school districts to my original table, which only lists nations, states, etc.

  7. #7
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kromey View Post
    It looks like your goal is to merely count how many rows you have where that URL appears, am I correct?
    I guess that's basically what I'm doing. The script I posted is followed by a three-part IF function...

    1. If the URL in my browser matches a value in my database, display matching information.

    2. If there are two or more values that match the URL (e.g. the state of Georgia and the Republic of Georgia), then let visitors choose which one they want.

    3. If the URL matches nothing in my database, display a 404 error page.

    Are you saying the script you posted would be better for that purpose than my original script? If so, I'll try it out; I'm using this same script on several websites, and some of them are definitely a bit slow loading.

    Thanks.

  8. #8
    An average geek earl-grey's Avatar
    Join Date
    Mar 2005
    Location
    Ukraine
    Posts
    1,403
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Then union can do the job:
    Code:
    SELECT 'geog_gw' AS type
         , name
      FROM gw_geog_gw
     WHERE gw_geog_gw.url = '$MyName'
    UNION ALL
    SELECT 'country' AS type
         , name
      FROM gw_counties 
     WHERE gw_counties.url='$MyName'

  9. #9
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry it took me so long to get my database tables squared away. Anyway, I'm still trying to iron out a few kinks. First, I thought I'd keep it simple by writing a script that only accesses one database table, replacing my original mysql_query with your SELECT COUNT(*) function. Can someone tell me if I did it correctly? It seems to be working OK, but I want to make sure I've got it right before I try to join both tables with a union. (Actually, it doesn't work when I try it with my second table alone, but that problem might be in my .htaccess file.)

    Thanks!

    * * * * *

    PHP Code:
    $MyName = ($_GET['area']);

    $result mysql_query("SELECT COUNT(*)
    FROM gw_geog_gw
    WHERE gw_geog_gw.URL = '
    $MyName'"$link);

    $num_rows mysql_num_rows($result);

    // MYSQL_NUM_ROW #1
    if (mysql_num_rows($result) === || empty($MyName))
    {
    echo 
    '<html>';
    include (
    $BaseInc."/MainPage.php");
    echo 
    '</html>';
    }

    // MYSQL_NUM_ROW #2 (Duplicate Values)
    elseif(mysql_num_rows($result) > 1)
    {
     include (
    $BaseINC."dupe.php");
    }

    // MYSQL_NUM_ROW #3 (No Matching Database Values)
    elseif(mysql_num_rows($result) === 0)
    {
     include (
    "/404.php");


  10. #10
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    At second glance, it isn't working right. When I use my original script...

    PHP Code:
    $result mysql_query("SELECT URL FROM gw_geog_gw
    WHERE URL = '
    $MyName'"$link); 
    ...the mysql_num_rows function correctly associates $MyName with values from my database table. If I type MySite/World/California into my browser, it works, but MySite/World/CaliforniaX yields a 404 error message, as it should.

    But if I use the SELECT COUNT method...

    PHP Code:
    $result mysql_query("SELECT COUNT(*)
    FROM gw_geog_gw
    WHERE gw_geog_gw.URL = '
    $MyName'"$link); 
    ...then anything goes. It treats CaliforniaX like a value from my database table. In other words, I can type anything into my browser without getting a 404 error message.

    Thanks.

  11. #11
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Or am I supposed to replace my mysql_num_rows function with a mysql_result script? If so, can someone show me what it would look like, including the beginning COUNT (*) function? I'm getting more confused.

  12. #12
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've now combined the two tables (and changed the first table to gw_geog) with the following script:

    PHP Code:
    $MyName = ($_GET['area']);

    $result mysql_query("SELECT * FROM
    (
     SELECT GW.Name AS URL FROM gw_geog GW
     UNION ALL
     SELECT SMG.URL FROM sm_geog SMG
    )
     AS SMGeog
     WHERE URL = '
    $MyName'");

    $num_rows mysql_num_rows($result);

    // MYSQL_NUM_ROW #1
    if (mysql_num_rows($result) === || empty($MyName))
    {
    echo 
    '<html>';
    include (
    $BaseInc."/MainPage.php");
    echo 
    '</html>';
    }

    // MYSQL_NUM_ROW #2 (Duplicate Values)
    elseif(mysql_num_rows($result) > 1)
    {
    include (
    $BaseINC."dupe.php");
    }

    // MYSQL_NUM_ROW #3 (No Matching Database Values)
    elseif(mysql_num_rows($result) === 0)
    {
    include (
    "/404.php");

    It works, but I'm still trying to figure out how to convert it to Count(*)/mysql_result. Thanks.
    Last edited by geosite; May 13, 2007 at 02:59.

  13. #13
    An average geek earl-grey's Avatar
    Join Date
    Mar 2005
    Location
    Ukraine
    Posts
    1,403
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you want just the count, you can use this query:
    Code:
    SELECT ( ( SELECT COUNT(*)
                 FROM gw_geog_gw
                WHERE gw_geog_gw.url = '$MyName' ) +
             ( SELECT COUNT(*)
                  FROM gw_counties 
                 WHERE gw_counties.url='$MyName' ) ) AS value_count
    Also, you need to escape text values, which are are used in the queries, with mysql_real_escape_string() to protect your script from SQL injection.

  14. #14
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by earl-grey View Post
    If you want just the count,
    I'm not sure if I just want the count or not; is that what I need to do to make this script work with mysql_num_rows?

    you can use this query:
    Code:
    SELECT ( ( SELECT COUNT(*)
                 FROM gw_geog_gw
                WHERE gw_geog_gw.url = '$MyName' ) +
             ( SELECT COUNT(*)
                  FROM gw_counties 
                 WHERE gw_counties.url='$MyName' ) ) AS value_count
    Also, you need to escape text values, which are are used in the queries, with mysql_real_escape_string() to protect your script from SQL injection.
    Thanks, but how does that display pages associated with United_States, Japan, etc.? Surely I have to somehow relate it to my mysql_num_rows script. I tried the following, but it doesn't work:

    PHP Code:
    $result mysqlJ_query("SELECT
    (SELECT COUNT(*) FROM gw_geog GW
     WHERE gw_geog.Name = '
    $MyName' ) +
    (SELECT COUNT(*)
    FROM sm_geog
    WHERE sm_geog.URL = '
    $MyName')
    AS value_count"

    Maybe I should start a new thread, as this one is getting hopelessly confusing. But you've all given me some good tips, though. It sounds like I have replace my $result script with a COUNT(*) function and/or replace my mysql_num_rows script with a mysql_result script. Thanks.


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
  •