1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Need help retrieving results from certain table

Discussion in 'MySQL' started by jcanes, Jan 11, 2013.

  1. #1
    As thread title says, currently using this code to pull in listings from the "listings" table in the database
    <?PHP
    
    if ($_POST[limit] != $nil) 
            $_SESSION['limit'] = $_POST['limit'];
            
    if ($_SESSION[limit] == $nil)
            $_SESSION['limit'] = 20;
    //---------------------------------------
    ?>
    <?PHP include "bit_centerbar.php"; ?>
    
    
    
    
    <?php
    //-------------------------
    if (empty($rowxxx['SORT']))
            $rowxxx['SORT'] = 'TITLE ASC';
    else {
            if ($rowxxx['SORT'] == Alphabetically)
                    $rowxxx['SORT'] = 'TITLE ASC';
            if ($rowxxx['SORT'] == 'Date Added')
                    $rowxxx['SORT'] = 'YA DESC, MA DESC, DA DESC, TITLE DESC';
            if ($rowxxx['SORT'] == 'Date Updated')
                    $rowxxx['SORT'] = 'YU DESC, MU DESC, DU DESC, TITLE ASC';
    }
    if (empty($_GET[sortby]))
            $sort = $rowxxx['SORT'];
            $_GET[sortby] = "$sort";
    $table = "LISTINGS";
    $string = "WHERE STATUS = 'Active' AND CATEGORY LIKE '%-$_GET[category]-%'";
    if (empty($_GET[category])) $string = "WHERE STATUS = 'Active'";
    if ($_GET[category] == '') $string = "WHERE ID != ''";
    $swap = "&page=$_GET[page]&category=$_GET[category]";
    
    
    //-------------------------------------------------------------------------------------------
    
    
    $query1         = @mysql_query ("SELECT * FROM $table INNER JOIN PACKAGES ON LISTINGS.PACKAGE = PACKAGES.ID $string $adv_search ORDER BY PACKAGES.RANK ASC, $_GET[sortby]");
    //$query1 = @mysql_query ("SELECT * FROM $table $string $adv_search ORDER BY $_GET[sortby]");
    $numrows= @mysql_num_rows ($query1);
    if (!isset ($_GET['show'])) {
            $display = 1;
    } else {
            $display = $_GET['show'];
    }
    
    
    $start  = (($display * $_SESSION['limit']) - $_SESSION['limit']);
    
    
    $query2         = @mysql_query ("SELECT *, LISTINGS.ID AS LID FROM $table INNER JOIN PACKAGES ON LISTINGS.PACKAGE = PACKAGES.ID $string $adv_search ORDER BY PACKAGES.RANK ASC, $_GET[sortby] LIMIT $start,$_SESSION[limit]");
    //$query2 = @mysql_query ("SELECT * FROM $table $string $adv_search ORDER BY $_GET[sortby] LIMIT $start,$_SESSION[limit]");
    while ($row = @mysql_fetch_array ($query2)) {
    
    
            include "browse_display.php";
    
    
    }
    //-------------------------------------------------------------------------------------------
    ?>
    
    
    <?php if ($numrows == '0') { ?>
    
    
    <div class="tdbox" style="width: 100%;"></div>
    <?php if (empty($_GET[category])) include 'bit_centerbar.php'; ?>
    
    
    <? echo $browse_no; ?>
    
    
    <?PHP } else { ?>
    
    
    <form action="?show=1&amp;page=browse&amp;category=<?php echo $_GET[category]; ?>&amp;mode=<?php echo $_GET[mode]; ?>" method="post" name="form" id="form">
    <div class="pagi" style="margin-top: 15px;">
    <input type="submit" name="Submit" value="<? echo $browse_show; ?>" />
    <select name="limit">
    <option selected> 
    <?php echo $_SESSION['limit']; ?>
    </option>
    <option>1</option>
    <option>5</option>
    <option>10</option>
    <option>20</option>
    <option>30</option>
    <option>50</option>
    <option>100</option>
    </select> <? echo $browse_lists; ?> <?php include "pagination_bot.php"; ?>
    </div>
    </form>
    
    
    <?PHP } ?>
    Code (markup):
    But now I am trying to find a way to display the results from particular states, i.e. Victoria, Queensland(this is from Australia).

    I am trying the following code, but currently getting the "no listings here, check back soon" message with no errors.

    I'm very new to PHP so not sure if I need to tweak the code to pull the results from the "LISTINGS" table, or the "LOCATIONS"


    <?PHP
    
    if ($_POST[limit] != $nil) 
            $_SESSION['limit'] = $_POST['limit'];
            
    if ($_SESSION[limit] == $nil)
            $_SESSION['limit'] = 20;
    //---------------------------------------
    ?>
    <?PHP include "bit_centerbar.php"; ?>
    
    
    
    
    <?php
    //-------------------------
    if (empty($rowxxx['SORT']))
            $rowxxx['SORT'] = 'TITLE ASC';
    else {
            if ($rowxxx['SORT'] == Alphabetically)
                    $rowxxx['SORT'] = 'TITLE ASC';
            if ($rowxxx['SORT'] == 'Date Added')
                    $rowxxx['SORT'] = 'YA DESC, MA DESC, DA DESC, TITLE DESC';
            if ($rowxxx['SORT'] == 'Date Updated')
                    $rowxxx['SORT'] = 'YU DESC, MU DESC, DU DESC, TITLE ASC';
    }
    if (empty($_GET[sortby]))
            $sort = $rowxxx['SORT'];
            $_GET[sortby] = "$sort";
    $table = "LISTINGS";
    $string = "WHERE STATUS = 'Active' AND CATEGORY LIKE '%-$_GET[STATE]-%'";
    if (empty($_GET[category])) $string = "WHERE STATUS = 'Active'";
    if ($_GET[category] == '') $string = "WHERE ID != 'Queensland'";
    $swap = "&page=$_GET[page]&state=$_GET[state]";
    
    
    //-------------------------------------------------------------------------------------------
    
    
    $query1         = @mysql_query ("SELECT * FROM $table INNER JOIN PACKAGES ON LISTINGS.PACKAGE = PACKAGES.ID $string $adv_search ORDER BY PACKAGES.RANK ASC, $_GET[sortby]");
    //$query1 = @mysql_query ("SELECT * FROM $table $string $adv_search ORDER BY $_GET[sortby]");
    $numrows= @mysql_num_rows ($query1);
    if (!isset ($_GET['show'])) {
            $display = 1;
    } else {
            $display = $_GET['show'];
    }
    
    
    $start  = (($display * $_SESSION['limit']) - $_SESSION['limit']);
    
    
    $query2         = @mysql_query ("SELECT *, LISTINGS.ID AS LID FROM $table INNER JOIN PACKAGES ON LISTINGS.PACKAGE = PACKAGES.ID $string $adv_search ORDER BY PACKAGES.RANK ASC, $_GET[sortby] LIMIT $start,$_SESSION[limit]");
    //$query2 = @mysql_query ("SELECT * FROM $table $string $adv_search ORDER BY $_GET[sortby] LIMIT $start,$_SESSION[limit]");
    while ($row = @mysql_fetch_array ($query2)) {
    
    
            include "bwose_display.php";
    
    
    }
    //-------------------------------------------------------------------------------------------
    ?>
    
    
    <?php if ($numrows == '0') { ?>
    
    
    <div class="tdbox" style="width: 100%;"></div>
    <?php if (empty($_GET[category])) include 'bit_centerbar.php'; ?>
    
    
    <? echo $browse_no; ?>
    
    
    <?PHP } else { ?>
    
    
    <form action="" method="post" name="form" id="form">
    <div class="pagi" style="margin-top: 15px;">
    <input type="submit" name="Submit" value="<? echo $browse_show; ?>" />
    <select name="limit">
    <option selected> 
    <?php echo $_SESSION['limit']; ?>
    </option>
    <option>1</option>
    <option>5</option>
    <option>10</option>
    <option>20</option>
    <option>30</option>
    <option>50</option>
    <option>100</option>
    </select> <? echo $browse_lists; ?> <?php include "pagination_bot.php"; ?>
    </div>
    </form>
    
    
    <?PHP } ?>
    Code (markup):
     
    jcanes, Jan 11, 2013 IP
  2. Rukbat

    Rukbat Well-Known Member

    Messages:
    2,908
    Likes Received:
    37
    Best Answers:
    51
    Trophy Points:
    125
    #2
    Are your states in the GET formatted like

    -Victoria-

    That's what your WHERE clause claims. If it's just

    Victoria

    lose the hyphens in the LIKE.
     
    Rukbat, Jan 11, 2013 IP
  3. jcanes

    jcanes Peon

    Messages:
    3
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Like this??
    $table = "LISTINGS";$string = "WHERE STATUS = 'Active' AND CATEGORY LIKE '%$_GET[STATE]%'";
    if (empty($_GET[category])) $string = "WHERE STATUS = 'Active'";
    if ($_GET[category] == '') $string = "WHERE ID != 'Victoria'";
    $swap = "&page=$_GET[page]&state=$_GET[state]";
    Code (markup):
    I tried with the URL /index.php?page=victoria&category=2&

    And it just lists all of the listings from category 2, it doesn't only display the Victoria ones.

    I'm very stumped.
     
    jcanes, Jan 11, 2013 IP