#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    40
    Rep Power
    3

    Querying through multiple tables in my database


    So I have another irritating situation. Down at $tablenames, I start trying to query through different tables in my database "meets". I'm very close. I have a dynamic sql statement that adds the name of each table into the query for everytime it loops. When I echo $sqlmeets and paste that into phpmyadmin, I get what I want, which is one row and its content. So that means that the sql statement each time around is correct. But when I try to return the results or at least the #rows of the result, I get nothing. Anybody see somewhere I went wrong? Thanks for the help!
    PHP Code:
    <?php

    DEFINE 
    ('DB_USER','#');
    DEFINE ('DB_PSWD','#');
    DEFINE ('DB_HOST','localhost');
    DEFINE ('DB_NAME1','phplogin2');
    DEFINE ('DB_NAME2','meets');
    DEFINE ('DB_NAME3','jumpers');

    $dbcon mysqli_connect(DB_HOSTDB_USERDB_PSWDDB_NAME1 );
    $dbcon2 mysqli_connect(DB_HOSTDB_USERDB_PSWDDB_NAME2 );
    $dbcon3 mysqli_connect(DB_HOSTDB_USERDB_PSWDDB_NAME3 );

    if (!
    $dbcon || !$dbcon2 || !$dbcon3) {
        die(
    "couldn't connect");    
    }



    ?>
    PHP Code:
    <?php
     error_reporting
    (E_ALL);  
     
    ini_set('display_errors''on'); 
     
    include(
    '/var/www/evan/includes/config.php');

    $jfn = (isset($_GET['jfn'])) ? $_GET['jfn'] : 'none';
    $jln = (isset($_GET['jln'])) ? $_GET['jln'] : 'none';
     if (
    $jfn == 'none' || $jln == 'none') { 
     
    header ("Location: http://#/evan/includes/history/profiles/home.php");
     }
     
         if (!
    $dbcon) {
            die(
    "couldn't connect");    
        }




     
    $biosql "SELECT * FROM `jumperprofiles` WHERE first_name LIKE '%".$jfn."%' AND last_name LIKE '%".$jln."%'";
     
    $bioquery mysqli_query($dbcon$biosql);
     
    $num_rows2 mysqli_num_rows($bioquery);

        echo 
    "
       <div id=\"historycontent\">
            <div id=\"youreherelinks\">
            <a href=\"http://#/evan/hjhindex.php\">
                High Jump History
            </a>
            &nbsp;
            >
            &nbsp;
            <a href=\"http://#/evan/includes/history/profiles/home.php\">
            Jumper Profiles
            </a>
            &nbsp;
            >
            &nbsp;
            <span id=\"blue\">"
    ;
            
            while(
    $row mysqli_fetch_assoc($bioquery)) {
              
            
    $jfn $_GET['jfn'];
            
    $jln $_GET['jln'];
            
    $firstname preg_replace("/[^A-Za-z]/","",$jfn);
            
    $lastname preg_replace("/[^A-Za-z]/","",$jln);
            echo 
    $firstname."&nbsp;".$lastname ;    
            
            
            echo 
    "    
            </span>
            
            <hr />
            </div>
            <div id=\"menhomemaindiv\">
                <div id=\"mencontentheader\">"
    ;
                
                
                  include 
    '/var/www/evan/includes/share.php'
                
                echo 
    "
                <h1 style=\"margin-bottom:0px !important;\"> "
    ;
                
                echo 
    $firstname."&nbsp;".$lastname;        
                
                echo

                </h1>
                       <div id=\"profilesdiv\">
                        <div id=\"profiletophalf\">                    
                            <div class=\"crop\">
                                <img id=\"profilephoto\" src=\" "
    ;
                                
                                                                
                                                                    echo 
    $row['profileurl'];
                                                                
                                                            echo 

                                                             \" />
                            </div>
                            <div id=\"jumperprofileinfo\">
                                <div id=\"infolabels\">
                               <table id=\"info\">
                                    <tr class=\"infotr\" id=\"nationality\">
                                        <td class=\"infotd\">Nationality
                                        </td>
                                    </tr>
                                    <tr class=\"infotr\" id=\"age\">
                                        <td class=\"infotd\">Age
                                        </td>
                                    </tr>
                                    <tr class=\"infotr\" id=\"heigh\">
                                        <td class=\"infotd\">Height
                                        </td>
                                    </tr>
                                    <tr class=\"infotr\" id=\"weight\">
                                        <td class=\"infotd\">Weight
                                        </td>
                                    </tr>
                                    <tr class=\"infotr\" id=\"PR\">
                                        <td class=\"infotd\">Personal Record
                                        </td>
                                    </tr>
                                    <tr class=\"infotr\" id=\"prheightdif\">
                                        <td class=\"infotd\">Height Differential
                                        </td>
                                    </tr>
                                    <tr class=\"infotr\" id=\"approach\">
                                        <td class=\"infotd\">Approaches from
                                        </td>
                                    </tr>
                                </table>
                                </div>
                                <div id=\"infoentries\">
                                <table id=\"info\">
                                    <tr class=\"infotr\" id=\"nationality\">
                                        <td class=\"infotd\">"
    ;
                                            if (!
    $row['nationality']) {
                                                    echo 
    " N/A ";
                                                    
                                                }else {
                                                    echo 
    "<img id=\"infonatl\" src=\"http://#/evan/pictures/history/olympic/men/smallflags/"
                                                    
    $lc strtolower($row['nationality']);
                                                    echo 
    $lc;
                                                    echo 
    ".png\" />
                                                    <a href=\"#\"> "
    .$row['nationality']."</a>";
                                                }
                                        echo
    "
                                        </td>
                                    </tr>
                                    <tr class=\"infotr\" id=\"age\">
                                        <td class=\"infotd\"> "

                                        
                                             
                                                
    $date_of_birth $row['dob'];
                                                
                                                if (!
    $row['dob']) {
                                                    echo 
    " N/A ";
                                                    
                                                }else {
                                             
                                            echo 
    CalculateAge($date_of_birth).'&nbsp;yrs&nbsp;('.$date_of_birth.')' ;
                                                }
                                        
                                        echo 
    "
                                        </td>
                                    </tr>
                                    <tr class=\"infotr\" id=\"heigh\">
                                        <td class=\"infotd\">"

                                        
                                        if (!
    $row['height']) {
                                            echo 
    " N/A ";
                                                }else {
                                                
    inchesTometers($row['height']);    
                                                echo 
    " ("
                                                
    inchesToFeetInches($row['height']);
                                                echo 
    ")";
                                                }
                                            echo 
    "
                                        </td>
                                    </tr>
                                    <tr class=\"infotr\" id=\"weight\">
                                        <td class=\"infotd\"> "
    ;
                                        if (!
    $row['weight']) {
                                                    echo 
    " N/A ";
                                                    
                                                }else {
                                                    echo 
    lbsTokg($row['weight'])." (".$row['weight']."lbs)";
                                                }
                                        echo 
    "
                                        </td>
                                    </tr>
                                    <tr class=\"infotr\" id=\"PR\">
                                        <td class=\"infotd\"> "
    ;
                                        if (!
    $row['PR']) {
                                                    echo 
    " N/A ";
                                                    
                                                }else {
                                                    
    $pr formatPR($row['PR']);
                                                    echo 
    $pr;
                                                    echo 
    "m (";
                                                    echo 
    centimetersToFeetInches($row['PR']);
                                                    echo 
    ")";
                                                }
                                        echo 
    "</td>
                                    </tr>
                                    <tr class=\"infotr\" id=\"prheightdif\">
                                        <td class=\"infotd\">"
    ;
                                        if (!
    $row['height'] || !$row['PR']){
                                            echo 
    " N/A ";
                                            } else {
                                                
                                                
                                                echo 
    inchesTocentimeters($row['height'],$row['PR']);
                                            }
                                        echo
    "
                                        </td>
                                    </tr>
                                    <tr class=\"infotr\" id=\"approach\">
                                        <td class=\"infotd\">Right
                                        </td>
                                    </tr>
                                </table>
                                </div>
                            </div>
                        </div>
                        <div id=\"profilebottomhalf\">
                            <div class=\"tabbed_content\">
        <div class=\"tabs\">
            <div class=\"moving_bg\">
                &nbsp;
            </div>
            <span class=\"tab_item\">
                Biography
            </span>
            <span class=\"tab_item\">
                Performances
            </span>
            <span class=\"tab_item\">
                Pictures
            </span>
            <span class=\"tab_item\">
                Video
            </span>
        </div>
     
        <div class=\"slide_content\">
            <div class=\"tabslider\">
                <ul>
                    <li>"
    ;   
                        
    $tablenames "SHOW TABLES FROM meets";
                        
    $tablequery mysqli_query($dbcon2$tablenames);
                        
    $num mysqli_num_rows($tablequery);
                        
    $whole_name $jln.", ".$jfn ;
                        while(
    $row3 mysqli_fetch_row($tablequery)) {
                        
                        echo (
    'Table ' $row3[0] . '<br /><table>');
                        
    $sqlmeets"SELECT * FROM `".$row3[0]."` WHERE `name` = '$whole_name'";
                        
    $result2 mysqli_query($dbcon2$sqlmeets);
                        
    $numrows mysqli_num_rows($result2);
                        echo 
    "<br> ".$numrows;
                            if (
    mysqli_num_rows($result2) >= 1){
                            echo (
    '<tr>'); 
                                while (
    $row2 mysqli_fetch_row($result2)) {
                                    for (
    $i=0$i<mysqli_num_fields($result2); $i ++){
                                    echo (
    '<td>' $row2[$i] . '</td>');
                                    }
                                }
                            echo (
    '</tr>'); 
                            } else {
                            echo (
    '<tr><td colspan="999">No records for table ' $row3[0] . '</td></tr>'); 
                            }
                        echo (
    '</table>');
                        }
                        echo 
    "
                    </li>
                </ul>
                <ul>
                    <li>
                        <div rel=\"scrollcontent1\">
                        <p id=\"paragraph-A\">"
    ;
                        echo 
    $row['bio'];
                        echo 
    "</p>
                    </li>
                </ul>
                <ul>
                    <li>
                        This matches the third tab
                    </li>
                </ul>
                <ul>
                    <li>
                        This matches the fourth tab
                    </li>
                </ul> 
            </div>
        </div>
    </div>


                        </div>
                    </div>
                
                 </div>
                
            </div>
            
        </div>"
    ;
            }
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by jel5363
    When I echo $sqlmeets and paste that into phpmyadmin, I get what I want, which is one row and its content. So that means that the sql statement each time around is correct.
    so it's not a mysql problem

    moving thread to php forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    What do you mean by "nothing"? What do var_dump($result2) and var_dump($numrows) say?

    Apart from that, your code has massive security holes. It's vulnerable to both cross-site scripting and SQL injections, since you neither use prepared statements nor do any escaping. For example, you take $jln and $jfn directly from the GET parameters and simply dump them into the query string. That's a very bad idea, because it allows an attacker to manipulate the query and e. g. fetch all user passwords.

    So you should definitely read up on security precautions before you put the code anywhere near an online server.

    There are several other problems:
    • The whole database setup where you connect to multiple databases and basically dump all tables points to severe misdesign. It looks like you misuse databases to group similar tables.
    • Use the appropriate fetch methods (like fetch_assoc)
    • Avoid cryptic and meaningless variable names
    • Use proper indentation
    • Use single quotes for strings when they contain many double quotes to avoid escaping
    • Consider using a template engine like Smarty to avoid the PHP-HTML spaghetti code

    So without wanting to frustrate you, I don't think you're "very close". Even when you get your code working, it still needs quite some refactoring.
    Last edited by Jacques1; September 17th, 2012 at 08:21 AM.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    40
    Rep Power
    3
    Well thats awful to finally hear from someone but i know exactly what you're saying because I've known all along that all my coding is a bit empty to say the least, but I'm completely self taught so I guess I can't blame myself. Var dump for result 2 looks like this
    PHP Code:
    object(mysqli_result)#7 (5) { ["current_field"]=> int(0) ["field_count"]=> int(9) ["lengths"]=> NULL ["num_rows"]=> int(0) ["type"]=> int(0) } 
    and for numrows it looks like this: "int(0)" without the quotes. What are some good places to start looking at for security precautions? Thanks for the honest opinion Jaques1
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by jel5363
    Well thats awful to finally hear from someone but i know exactly what you're saying because I've known all along that all my coding is a bit empty to say the least, but I'm completely self taught so I guess I can't blame myself.
    Those are all things you can fix and learn from, so I wouldn't worry about it. The main thing is to keep learning and improving.

    But as to security, you should actually check that before you go on, because that's not really something you should do with trial and error.



    Originally Posted by jel5363
    Var dump for result 2 looks like this
    Then there's obviously no row in the result set. The query doesn't find anything for those parameters (output the query and try it again in phpmyadmin).



    Originally Posted by jel5363
    What are some good places to start looking at for security precautions? Thanks for the honest opinion Jaques1
    The OWASP Top 10 is a pretty good overview of security problems and solutions. You'll find further information on the specific topics in Wikipedia. And when you search for the keywords in e. g. php.net, you should see how the security techniques are implemented in PHP.
    Last edited by Jacques1; September 17th, 2012 at 09:24 AM.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

IMN logo majestic logo threadwatch logo seochat tools logo