display unrelated data from 2 tables

Question

Trying to get data to display from 2 different and basically unrelated tables. Don't want them joined, just discrete output to look like this in a select box:

Category: current category from table1 (this part works)

Entire list of categories from table 2 (correct number of empty option tags, no data showing)

DB connection code:

    //DB CONNECTION//
    $dbcnx = mysqli_connect("localhost", $DBASEUSER, $DBASEPASSWORD, $DBASE);

    //Main Showcase

    $sql = "SELECT * FROM `dprods`";
//////HEADER in main page    
    <?php
    $ID=$_GET['ID'];
    $CAT=$_GET['cat'];
    ?>
   //////Main SECTION 
            <?php
    require "db_conn.php";    
    $sql2 = "SELECT * FROM $DBTABLE WHERE ID=$ID";
    $getinfo = mysqli_query($dbcnx, $sql2);
    $row = mysqli_fetch_assoc($getinfo);
    $PROD = $row['dtitle'];
    $PRICE = $row['dprice'];
    $PP = $row['dpplink'];


    echo "<tr> 
          <td width=\"12%\"> 
            <div align=\"right\"><b><font face=\"Arial, Helvetica, sans-serif\" size=2>Product 
              Category: &nbsp;&nbsp;</font></b></div>
          </td>
          <td colspan=2 width=\"88%\"><select name=\"dcat\"><option value=\"$CAT\">Current Category: $CAT</option>";
    $dcat = "SELECT * FROM 'dcat'";
    $getcat = mysqli_query($dbcnx, $dcat);
    $row2 = mysqli_fetch_assoc($getcat);
    while($row2 = mysqli_fetch_assoc($result))
    {    echo " <option value=\"".$row2["dcategory"]."\">".$row2["dcategory"]."</option>";
    }
    echo "".$row["dprice"]."</select>
        </tr>
        <tr> 
          <td width=\"12%\">&nbsp;</td>
          <td width=\"24%\">&nbsp;</td>
          <td width=\"64%\">&nbsp;</td>
        </tr>
        <tr> 
          <td width=\"12%\"> 
            <div align=\"right\"><b><font face=\"Arial, Helvetica, sans-serif\" size=2>Product 
              Title: &nbsp;&nbsp;</font></b></div>
          </td>
          <td width=\"24%\"> <b><font face=\"Arial, Helvetica, sans-serif\" size=2> 
            <input type=\"text\" name=\"Title\" value=\"".$row["dtitle"]."\" size=35 maxlength=25>
            </font></b><b><font face=\"Arial, Helvetica, sans-serif\" size=2> &nbsp;&nbsp;</font></b></td>
          <td width=\"64%\"><b><font face=\"Arial, Helvetica, sans-serif\" size=2>Price</font></b>: 
            &nbsp;&nbsp; <b><font face=\"Arial, Helvetica, sans-serif\" size=2> 
            <input type=\"text\" name=\"Price\" value=\"".$row["dprice"]."\" maxlength=10 size=20>
            </font></b></td>
        </tr>
        <tr> 
          <td width=\"12%\">&nbsp;</td>
          <td width=\"24%\">&nbsp;</td>
          <td width=\"64%\">&nbsp;</td>
        </tr>
        <tr> 
          <td width=\"12%\"> 
            <div align=\"right\"><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=2><b>PayPal 
              Link:&nbsp;&nbsp;&nbsp;</b></font></div>
          </td>
          <td colspan=2><font face=\"Arial, Helvetica, sans-serif\"><b>http://www.paypal.com</b> 
            </font> 
            <input type=\"text\" name=\"PP\" value=\"".$row["dpplink"]."\" size=50>
            <font face=\"Arial, Helvetica, sans-serif\">Only put in what follows paypal.com</font></td>
        </tr>

    ";
    mysqli_close($dbcnx);
    ?>

Been working and researching this for hours and now I'm so horribly confused, lupus induced Swiss-cheese brain isn't helping. I think I'm very close, just unable to get the data from the 2nd table to display. Would appreciate any suggestions, but please keep it as simple as possible. Some of the complex code I've seen in my research confuses me even more. Thanks!

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Apr 02 '18 at 17:54
You're selecting `dcategory` but using `$row2["getcat"]`. You might need `SELECT 'dcategory', 'getcat'` or `SELECT *` — adprocas, Apr 02 '18 at 17:57
Thank you adpro!!!! That was it. Working like a charm. Knew it was something simple, but couldn't see it. This new mysqli syntax has been whipping by butt. — user9587282, Apr 02 '18 at 18:01
No problem @user9587282 - please do make sure you look at the [SQL Injection Attacks link above](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). That's VERY important, and I would stop writing this code until you consider that. I did make an answer for this below, but the injection stuff should be top priority. — adprocas, Apr 02 '18 at 18:06
I will look into that and see if I can understand it. At least this part isn't an INSERT command. I'll see if I can find a simple procedural code sample. That PDO code is like Urdu to me right now. — user9587282, Apr 02 '18 at 18:14
SQL Injection hacks don't care if you're doing an insert. Some of the largest exploits were to hack into accounts by manipulating queries through unchecked input. Sometimes full other queries can be injected. Someone could add a `;`, ending your query, and then add a delete query, for example. — adprocas, Apr 02 '18 at 18:35

adprocas · Answer 1 · 2018-04-02T18:46:51.710

0

Try including getcat in your select query like this:

$dcat = "SELECT 'dcategory', 'getcat' FROM 'dcat'";

$getcat = mysqli_query($dbcnx, $dcat);

while($row2 = mysqli_fetch_assoc($getcat))
{
    echo " <option value=\"".$row2["getcat"]."\">".$row2["getcat"]."</option>";
}

echo "</select>"

You could also use $dcat = "SELECT * FROM 'dcat'";, but the above is likely slightly more efficient.

Also, I don't see where $result was being set for this code while($row2 = mysqli_fetch_assoc($result))

It looks like $result isn't set, so it would obviously give you undesirable results.

You're also doing $row2 = mysqli_fetch_assoc($getcat); outside of the while loop, which will grab the first row before entering the loop. The loop would start at the second row.

Please look at http://php.net/manual/en/mysqli-result.fetch-assoc.php

Also, your script will be subject to SQL Injection, as mentioned in the comments. You will need to fix it so it is as safe as possible. Read the links in the comments and also make sure you're using prepared statements

edited Apr 02 '18 at 18:46

answered Apr 02 '18 at 18:04

adprocas

1,863
1
14
31

One more small issue. The display is only showing 4 entries when there are 8 in that table. Can't figure out why, they are no limits in the code. – user9587282 Apr 02 '18 at 18:24
Ok, add the rest of your code and database information. There isn't much to go on with this code, as it isn't limiting anything as far as I can tell. – adprocas Apr 02 '18 at 18:36
Wait, I didn't catch this. you have a few things mixed up. Give me a minute. – adprocas Apr 02 '18 at 18:37
There you go - your `$result` didn't exist, so I doubt your while loop actually worked. I'm not sure what would happen there, as you weren't setting `$result`. And you were calling `mysqli_fetch_assoc` prior to the `while` loop. I'll add more explanations to my answer. – adprocas Apr 02 '18 at 18:39
put the whole thing above in original part. Gotta go for now, Dr. appt. will check back later. Thank you so much! I wasn't kidding when I said I was confused. – user9587282 Apr 02 '18 at 18:50

score 0 · Answer 2 · answered Apr 04 '18 at 16:13

adpro, your help was invaluable. I was more confused than I realized and your comments helped point that out to me. I went back & started from scratch with a clean query code that I could print out, mark up with colored hi-liters to follow the variables. Code now works as desired and I'm posting for anyone else who may need the clarification. I appreciate the input from everyone about the sql injection. My goal was to get the simple functionality working first before making it more complicated. Now I can add the protection to prevent sql injection.

<?php
require "db_conn.php";    
$sql2 = "SELECT * FROM $DBTABLE WHERE ID=$ID";
$getinfo = mysqli_query($dbcnx, $sql2);
$row = mysqli_fetch_assoc($getinfo);
$PROD = $row['dtitle'];
$PRICE = $row['dprice'];
$PP = $row['dpplink'];


echo "<tr> 
      <td width=\"12%\"> 
        <div align=\"right\"><b><font face=\"Arial, Helvetica, sans-serif\" size=2>Product 
          Category: &nbsp;&nbsp;</font></b></div>
      </td>
      <td colspan=2 width=\"88%\"><select name=\"dcat\"><option value=\"$CAT\">Current Category: $CAT</option>";

    $sqlCAT = "SELECT * FROM dcat";

    if($resultCAT = mysqli_query($dbcnx, $sqlCAT)){

        if(mysqli_num_rows($resultCAT) > 0){

            while($rowCAT = mysqli_fetch_array($resultCAT)){

                    echo "<option value=\"".$rowCAT['dcategory']."\">".$rowCAT['dcategory']."</option>";
            }

            // Free result set

            mysqli_free_result($resultCAT);

        } else{
            echo "No records matching your query were found.";
        }

    } else{
        echo "ERROR: Could not able to execute $sqlCAT. " . mysqli_error($dbcnx);
    }

echo " </select>";

mysqli_close($dbcnx);
?>

display unrelated data from 2 tables

2 Answers2