Search Query with different SQL table

[doran_lum]

I'm trying to create a search form for a CD collection from two tables within the same SQL.

The search form for the Category portion, there are over 50 category, do i have to manual enter each or there's a way to make it dynamic ?

    <form id = "getCD" method = "get"
        action = "SearchBy.php">

    Title
    <input type = "search" name = "CDTitle" />

    Year
    <input type = "search" name = "CDYear" />

    Category
    <select name="CDCategory">
        <option value="Acid Jazz"> </option>
        <option value="Acid Jazz">Acid Jazz</option>
        <option value="Afro-Beat">Afro-Beat</option>
    </select>

    <input type = "submit" value = "Search" />

</form>

For the 2nd portion in Search.php, I'm struggling with getting both table to join. I'm trying SQL ... JOIN...ON but encounter error.

<?php

include 'database_conn.php';		// make db connection

  $pCDTitle = $_GET['CDTitle'];
  $pCDYear = $_GET['CDYear']; 
  $pCDCategory = $_GET['CDCategory'];           // store the parameter in a variable

$sqlCD = "SELECT nmc_cd.CDID, nmc_cd.CDTitle, nmc_cd.CDYear, nmc_cd.CDPrice 
          FROM nmc_cd
          JOIN nmc_category on (nmc_cd.catID = nmc_category.catID)
          WHERE nmc_cd.CDTitle = '$pCDTitle' OR nmc_cd.CDYear = '$pCDYear' OR nmc_category.catDesc = '$pCDCategory'";

$rsCD = mysqli_query($conn, $sqlCD) 
or die(mysqli_error($conn));

while ($CD = mysqli_fetch_assoc($rsCD)) {
		$title = $CD['CDTitle'];
                $year = $CD['CDYear'];
                $price = $CD['CDPrice'];
                $category = $CD['CDCategory'];
		echo "<div>$title, $year, $price, $category</div>\n";
}
mysqli_free_result($rsCD); 
mysqli_close($conn);
?>

[ATTACH]5259[/ATTACH]

[ATTACH]5261[/ATTACH]

nmc_cd.jpg nmc_cat.jpg

[Bonesnap]

doran_lum;11051823 wrote:

The search form for the Category portion, there are over 50 category, do i have to manual enter each or there's a way to make it dynamic ?

You could write a SQL query to retrieve the categories and then in your PHP just run a loop to output your <option></option> tags.

doran_lum;11051823 wrote:

I'm struggling with getting both table to join. I'm trying SQL ... JOIN...ON but encounter error.

We really need to see the error to help you out here. At first glance your query looks fine. Always double-check column and table names are correct.

[doran_lum]

It's showing the error below where they can't find CDCategory -> $category = $CD['CDCategory'];
I check and the column seems ok except this category is from a different table.

Notice: Undefined index: CDCategory in C:\xampp\htdocs\AssignmentPartA\SearchBy.php on line 21
Amp, 2000, 9.20,

Notice: Undefined index: CDCategory in C:\xampp\htdocs\AssignmentPartA\SearchBy.php on line 21
Exist Dance: Transmitting From Heaven, 2000, 8.30,

Notice: Undefined index: CDCategory in C:\xampp\htdocs\AssignmentPartA\SearchBy.php on line 21
Let The Freak Flag Fly, 2000, 10.00,

[Bonesnap]

Ah, I see the problem.

Take a look at your query. You aren't selecting CDCategory. You are only selecting CDID, CDTitle, CDYear, and CDPrice. Can't output what you don't ask for :p

[doran_lum]

Thanks... i already add in catID

$sqlCD = "SELECT nmc_cd.CDID, nmc_cd.CDTitle, nmc_cd.CDYear, nmc_cd.CDPrice, nmc_cd.catID 
          FROM nmc_cd

But somehow i still can't fetch catDesc which belongs to nmc_category table..

while ($CD = mysqli_fetch_assoc($rsCD)) {
		$title = $CD['CDTitle'];
                $year = $CD['CDYear'];
                $price = $CD['CDPrice'];
                $category = $CD['catDesc'];

[doran_lum]

Thanks I manage to resolve it

[doran_lum]

Is it possible to run php within the search box ?
I'm getting the error -> Undefined variable: conn in C:........ on line 16 Warning: mysql_query() expects parameter 1 to be mysqli, null given in C:\ .......

Does the error means I have not yet define $rsCD properly ?

<form id = "getCD" method = "get"
        action = "SearchBy.php">

    Title
    <input type = "search" name = "CDTitle" />

    Year
    <input type = "search" name = "CDYear" />

    Category
    <select name="CDCategory">
        <option value="">
        <?php 
        include 'database_conn.php';
         $rsCD = mysqli_query($con, "SELECT nmc_category.catDesc FROM nmc_category");
         while($CD = mysqli_fetch_array($rsCD)){
         echo "<option value='".$result[0]."'>".$result[0]."</option>";
         }
         ?>
    </select>

    <input type = "submit" value = "Search" />

</form>
{/PHP]

[Bonesnap]

Take a look at the error message again. It says the variable $conn isn't defined, not $rsCD, though it's puzzling because the code you posted shows $con being used. Is there more code above the form?

[dalecosp]

$con != $conn ??

[doran_lum]

Sorry for the confusion. This is the 1st php which will display the search page. Previously i was using <option value="Acid Jazz">Acid Jazz</option> which is not a good option as I need to manually enter each entry. I would want to try to use the SQL query to retrieve the categories. Sorry for the typo in $con, i have correct that.

There are two PHP code so i'm just displaying the 1st portion for the search form.

nmc_category -> this is the SQL table
catDesc -> this is the column name

<form id = "getCD" method = "get"
        action = "SearchBy.php">

    Title
    <input type = "search" name = "CDTitle" />

    Year
    <input type = "search" name = "CDYear" />

    Category
    <select name="CDCategory">
        <option value="">
        <?php 
        include 'database_conn.php';
         $rsCD = mysqli_query($conn, "SELECT nmc_category.catDesc FROM nmc_category");
         while($CD = mysqli_fetch_array($rsCD)){
         echo "<option value='".$result[0]."'>".$result[0]."</option>";
         }
         ?>
    </select>

    <input type = "submit" value = "Search" />

</form>        

[dalecosp]

OK, fine.

You have no error testing, so no way of knowing if A] your $conn is actually a working MySQLi object, or B] $rsCD actually is a successful query.

Die() should never be used in production, but for development it can be handy. Check this:

<form id = "getCD" method = "get"
        action = "SearchBy.php">

    Title
    <input type = "search" name = "CDTitle" />

    Year
    <input type = "search" name = "CDYear" />

    Category
    <select name="CDCategory">
        <option value="">
        <?php 
        include 'database_conn.php';
         if (! ( is_object($conn ) && ( get_class( $conn ) == 'mysqli' )) {
            die("DB connection failure.");
         }
         $rsCD = mysqli_query($conn, "SELECT nmc_category.catDesc FROM nmc_category");
         if ( !$rsCD ) {
            die("No result from DB query."); //probably invalid SQL, table error, etc.
         }
         if ( $rsCD->num_rows < 1 ) {  
            die("No rows returned from DB query."); //query runs OK, but nothing is found in DB to match.
         }
         while($CD = mysqli_fetch_array($rsCD)){
         echo "<option value='".$result[0]."'>".$result[0]."</option>";
         }
         ?>
    </select>

    <input type = "submit" value = "Search" />

</form>

Lastly, and I'm not sure about this, but do you really need to fully qualify the field "catDesc"?

[doran_lum]

yes no worries it's only for development.

I'm not sure but I fear that if i don't fully qualify "catDesc" it, i might get undefined variable error.

I tested your and get error that $result is undefined.

[Weedpacket]

             while($CD = mysqli_fetch_array($rsCD)){
             echo "<option value='".$result[0]."'>".$result[0]."</option>";
             }

You're trying to use [font=monospace]$result[/font] but that's not where you're putting the results you get.

[doran_lum]

Weedpacket;11051909 wrote:

             while($CD = mysqli_fetch_array($rsCD)){
             echo "<option value='".$result[0]."'>".$result[0]."</option>";
             }

You're trying to use [font=monospace]$result[/font] but that's not where you're putting the results you get.

ahh ok ... i saw it .. thank you

[doran_lum]

Can I limit the display of my table to just 3/4 of the top webpage but still user is able to scroll down to see the rest of the table ? I saw it's possible with width but somehow it's not working for height.

<PHP>
echo '<table class=table table-striped table-bordered table-hover table align="center" table height="75%">';
</PHP>

I was trying to also color the text in my table but because i'm echoing the $row. I was trying to use FONT & /FONT but it doesn't work.

<PHP>
echo "</td><td>";

echo $row['CDYear'];
echo "</td><td>";

echo $row['CDPrice'];
</PHP>

[dalecosp]

Please use the board's [code=php] tags for code.

dorum lum wrote:

Can I limit the display of my table to just 3/4 of the top webpage but still user is able to scroll down to see the rest of the table ? I saw it's possible with width but somehow it's not working for height.

I'd wrap the table in a scrollable DIV:

<div style = "height:450px;overflow:scroll;">
<?php

make_my_table(); /// whatever you do to make a table

?></div>