WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] Database custom table issues - incorrect result displayed (8 posts)

  1. Robodashy
    Member
    Posted 3 months ago #

    I tried to put code in the backticks like it says, so just ignore any ''s that look out of place (unless it's blatantly not me trying to do that)

    First things first:
    I've spent most of today going over these forums;
    the stackoverflow forums;
    the php.net manual; and
    a bunch of random sites I found through Google Search - so,
    if I've missed the answer and it seems like I'm asking an already answered question I'm sorry.
    I've probably just been looking at the same problem for way too long and nothing is making sense.

    The background:

    We've inserted some custom tables into the wordpress database, so I can connect to these tables through $wpdb. The code for the working connections is as follows:

    'global $wpdb;
    // get list of localities
    $localityRow = $wpdb->get_results( "SELECT * FROM tblYBSLocality ORDER BY Name ASC" );
    // get list of schools
    $schoolRow = $wpdb->get_results( "SELECT * FROM tblYBSSchool ORDER BY Name ASC" );
    // get school ID if form has been submitted
    $SelectedSchoolID = $_GET['schools'];
    // get location ID if form has been submitted
    $SelectedLocationID = $_GET['localities'];'

    Then later in my page I make it do stuff (scorcery!)

    '// Create a drop down box for the user to select their location (and one has been done for school but I left that out because you get the idea
    <select name="localities" style="width: 300px">
    <?php
    foreach ($localityRow as $locality) {
    $LocalityID = $locality->LocalityID;
    $LocalityName = $locality->Name;
    ?>

    <option value=<?php echo $LocalityID; ?> <?php if(isset($SelectedLocationID) &&
    $SelectedLocationID == $LocalityID) print(" selected")?>><?php echo $LocalityName;?>
    </option>
    <?php
    }
    ?>
    </select>'

    Note - Yes I know using * is bad, I'll fix that later - right now I just want it to work.

    Using this I can then go through and generate a bunch of stuff using the following:

    '// get school ID if form has been submitted
    $SelectedSchoolID = $_GET['schools'];
    // get location ID if form has been submitted
    $SelectedLocationID = $_GET['localities'];'

    This isn't really important to the rest of my issue, so I'll move on.

    The issue:

    I'll start with the code. This is supposed to connect to some other tables and do stuff - based on the connections and data retrieved above:

    '$selectedSchoolName = $wpdb->get_results( "SELECT Name FROM tblYBSSchool WHERE SchoolID=".$SelectedSchoolID );
    // get selected location name
    $selectedLocationName = $wpdb->get_results( "SELECT Name FROM tblYBSLocality WHERE LocalityID=".$SelectedLocationID );
    // get route ID
    $getRouteID = $wpdb->get_results( "SELECT * FROM tblYBSRoute WHERE RouteID IN ( SELECT RouteID FROM tblYBSRouteSchool WHERE SchoolID =".$SelectedSchoolID.")".$SelectedLocationID);'

    Now, I THINK the issue comes with the ".$SelectedSchoolID ); part (and the location one but this is already getting TLDR).

    What I want to do later in the page (once the user has made their selections and submitted the form), is get results (fancy that!).

    There are 2 parts to the results:

    1st I want to say "Search results for routes between:" the location name and the school name.
    2nd, I want to display those results. But I can't even get past the 1st point, so let's fix that first.

    I have tried 3 different approaches:

    '// FIRST APPROACH
    // This kinda works, it echoes the last locality name from the table which is better than nothing
    <b><?php echo $LocalityName;?></b> and <b><?php echo $SchoolName;?></b>

    // SECOND APPROACH
    // This gives me the white page of death. Which is hilarious because it's pretty much the exact same as what was used in the option - which worked
    <b><?php if(isset($SelectedLocationID) && $SelectedLocationID == $LocalityID) echo $LocalityName;?></b> and <b><?php if(isset($SelectedSchoolID) && $SelectedSchoolID == $SchoolID) echo $SchoolName;?></b>

    // THIRD APPROACH
    // This also gives me the white page of death. And now I'm out of ideas.
    <b><?php if(isset($SelectedLocationID) && $selectedLocationName == $LocalityName echo $LocalityName ;?></b> and <b><?php if(isset($SelectedSchoolID) && $selectedSchoolName == $SchoolName echo $SchoolName ;?></b>'

    In my head (HAH!) the last 2 should work. Am I correct in thinking that it's the ".$SelectedSchoolID ); part? Or have I forked something else up entirely?

    Sorry for the TLDRness, but I thought I should provide as much detail as possible.
    Any and all help is greatly appreciated. I'm going to go smack my head into the desk a few times now :P

  2. bcworkz
    Member
    Posted 3 months ago #

    You appear to have used single quotes instead of back ticks for your code, and the forum's parser may have corrupted your code, so I can't really point out syntax errors. The back tick looks like a grave accent, slanting up to the left. It varies by keyboard where it is, but it is never on the key with the double quote.

    I can give you some general tips, maybe you can figure out the rest. Define WP_DEBUG as true on wp-config.php. Then PHP will tell you what and where your error is instead of just showing you a WSOD.

    Using $wpdb->get_results() returns an array of objects by default, so you must extract the value you want to compare before doing a comparison. var_dump() the returned values to get an idea how to reference individual values. For the var_dump() output to be properly formated, either use it inside of a <pre> block or view the output in source HTML mode.

    Good luck!

  3. Robodashy
    Member
    Posted 3 months ago #

    Excellent tip re the WP_DEBUG bcworkz, thanks heaps for that one. I'll change that and have a look at what errors I'm getting and see if that helps.

    Re the backticks - ahhhh derp on my behalf yeah found it. Wasn't really in the right headspace to be thinking clearly yesterday when I posted this. I've chucked the code sections into a pastebin: http://pastebin.com/4TqSBxm8 if that helps with any syntax issues.
    But I'll remember where the 's are for next time :)

    The errors I'm getting on the current WSOD's are as follows:
    WSOD example one which was
    <b><?php if(isset($SelectedLocationID) && $SelectedLocationID == $LocalityID) echo $LocalityName;?></b> and <b><?php if(isset($SelectedSchoolID) && $SelectedSchoolID == $SchoolID) echo $SchoolName;?></b>
    Gives me **Note, third edit - how about I give you relevant errors that happen once a search has been performed**:
    WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '7' at line 1]
    SELECT * FROM tblYBSRoute WHERE RouteID IN ( SELECT RouteID FROM tblYBSRouteSchool WHERE SchoolID =17)7

    So yeah, syntax errors. Cool beans.

    WSOD example two was:
    <b><?php if(isset($SelectedLocationID) && $selectedLocationName == $LocalityName echo $LocalityName ;?></b> and <b><?php if(isset($SelectedSchoolID) && $selectedSchoolName == $SchoolName echo $SchoolName ;?></b>
    Gives me:
    Parse error: syntax error, unexpected T_ECHO in //not giving the url/routesearch.php on line 147
    Line 147 is the above code, so not as helpful as the first error - but still better than nothing.

    Thanks again bcworkz.

  4. Robodashy
    Member
    Posted 3 months ago #

    Hmmmmmm, interesting. bcworkz, is there any ... next level error checking that WP can do inside wp_config?

    I "think" I fixed the syntax error, as I'm not getting that error anymore - but now I get no error and it doesn't work.... sooooo.... yeah? I'll keep plugging away at it, but here's what I've changed:
    From:
    $getRouteID = $wpdb->get_results( "SELECT * FROM tblYBSRoute WHERE RouteID IN ( SELECT RouteID FROM tblYBSRouteSchool WHERE SchoolID =".$SelectedSchoolID.")".$SelectedLocationID);

    To (removed the last point just to see what it does, it fixes the syntax error but doesn't do much else):
    $getRouteID = $wpdb->get_results( "SELECT * FROM tblYBSRoute WHERE RouteID IN ( SELECT RouteID FROM tblYBSRouteSchool WHERE SchoolID =".$SelectedSchoolID.")" );

  5. Robodashy
    Member
    Posted 3 months ago #

    BANG FRIKKIN BANG! Fixed it. Kinda.
    If I can't fix the next issue I'll make a new post, but big thanks to bcworkz for pointing out the debug option - made things so much easier.

    The problem was that I was using $wpdb->get_results on all of my calls to the database.
    Use $wpdb->get_var (get_row works too) when you are trying to select a specific row, not a bunch of rows.

  6. bcworkz
    Member
    Posted 3 months ago #

    :D Yeah, it helps if you ask for what you actually want. It's so anticlimactic after finding ones way over some huge hurdle to only find yet another hurdle to be surmounted (hopefully the last) before reaching the final goal of bug free code.

    One more note about WP_DEBUG. It's considered a slight security risk because hackers can use it to probe for your site's weaknesses and potential avenues of attack. You should not leave it defined as true on a live site for any appreciable length of time if you are not actively working on code. Not to mention the messages freak out regular visitors.

  7. vikramadityas60
    Member
    Posted 2 months ago #

    how i can create a new table and what name i have to give it and how can i connect it to submit form.

  8. Robodashy
    Member
    Posted 2 months ago #

    Hi Vikramadityas60.

    A couple of different ways you can do it.
    We created our additional tables through PHPMyAdmin, but I've provided a couple of links below which should help you through.

    http://codex.wordpress.org/Creating_Tables_with_Plugins
    http://code.tutsplus.com/tutorials/custom-database-tables-creating-the-table--wp-28124
    http://www.paulund.co.uk/creating-custom-tables-wordpress-plugin-activation

    And more importantly http://codex.wordpress.org/Class_Reference/wpdb

    Give it whatever name you want. As it is within the WordPress database you just have to specify the table name to make the connection. Take the code I've used as the example:

    $getRouteID = $wpdb->get_results( "SELECT * FROM tblYBSRoute WHERE RouteID IN ( SELECT RouteID FROM tblYBSRouteSchool WHERE SchoolID =".$SelectedSchoolID.")" );

    $getRouteID is the variable which runs the call to connect to the database.
    $wpdb is the WordPress DataBase class that talks to the WP database.
    ->get_results ... gets the results.
    Then you have your usual MySQL (ezSQL ish) calls:
    SELECT * (hot tip - don't use * for security reasons, this was done for demo purposes only) FROM tblYBSRoute (my table name) ... then on and on depending what you want to do.

    Hopefully that helps you get started, let me know if you need any more info (but as this question has been resolved I'd probably suggest you start your own thread).

    Thanks.

Reply

You must log in to post.

About this Topic