*Newbie* - Review of a small section of code and questions

[digicoder]

Hi All,

I am “very newbie” so I apologize for my code not being up to standards. I am sure that there are better ways to do what I am trying to do. However I would appreciate it if, before proposing a better way, you could look at my code and correct it first, so that I can understand my current mistakes before learning something else. I have a form where the user can choose a single value and then submit it:

<form action="index.php" method="post">
	   <p>Choose a category: <select name="formCat">
		   <option value="">Select...</option>
		   <option value="All in one">All in one</option>
		   <option value="Art">Art</option>
		   <option value="Books">Books</option>
		   <option value="GiftCards">Gift Cards</option>
		   <option value="Jewelry, Accessories & Apparel">Jewelry, Accessories & Apparel</option>
		   <option value="Food">Food</option>
		   <option value="Music">Music</option>
                   </select>
                   <input type="submit" name="formSubmit" value="Submit" />
          </p>
</form>

Depending on the user input, the corresponding query is run against the database and results printed to screen. I am doing my best to use PDO:

[php]
include_once “dbconn.php”;

    if (@$_POST['formCat'] == 'All in one'){
        $query = $db->query("SELECT * FROM titems WHERE titems.item_Cat_Id = 1");
        while ($row = $query->fetch(PDO::FETCH_ASSOC)){
            echo 'Name: ' . $row['item_Name'] . "<br />";
            echo 'Description: ' . $row['item_Desc'] . "<br />";
            echo 'Link: ' . $row['item_Url'] . "<br /><br />";
        }
    }elseif (@$_POST['formCat'] == 'Art'){
        $query = $db->query("SELECT * FROM titems WHERE titems.item_Cat_Id = 2");
            while ($row = $query->fetch(PDO::FETCH_ASSOC)){
                echo 'Name: ' . $row['item_Name'] . "<br />";
                echo 'Description: ' . $row['item_Desc'] . "<br />";
                echo 'Link: ' . $row['item_Url'] . "<br /><br />";
             }
        }

[/php]

My questions:

1. Do I still need to sanitize $_POST, even though in this case the value is not inserted into the database and the choices are limited to premade selections from the form?
2. If so, how would I do that?
3. How do I display the 3rd item (item_Url) in a URL format that users can click on? I tried:

[php]
echo 'Link: ’ . ‘<a href = "’ . $record[‘item_Url’] . ‘">’ . $record[‘item_Url’] . ‘

’;
[/php]

But when I click it, it wants to go to “http://localhost%22http://thewebsiteaddress.com%22”. I need it to go to “http://thewebsiteaddress.com” instead.

Thank you!

[JimL]

1: no
3: how does the data in the db look?

Using @ to suppress errors is not considered good practice. Errors should be handled, not suppressed.

You couldn’t do the queries like this? Then you don’t have to do a separate query/loop for each category.

[php]$sth = $dbh->prepare(‘SELECT * FROM titems WHERE titems.item_Title = ?’);
$sth->bindParam(1, $_POST[‘formCat’], PDO::PARAM_STR);

while($row = $sth->fetch()) {
echo 'Name: ’ . $row[‘item_Name’] . “
”;
echo 'Description: ’ . $row[‘item_Desc’] . “
”;
echo 'Link: ’ . $row[‘item_Url’] . “

”;
}[/php]

[digicoder]

1: Thank you
2: In the DB it looks like this (I hope it’s what you are looking for, otherwise let me know):

http://imgur.com/JwmdYaD

When I don’t use the @, I receive the following notice, if I visit the page for the first time:

Notice: Undefined index: formCat in C:\xampp\htdocs\Shibefieds\index.php on line 47

I think it’s because no data has been passed yet?

Looking at your code next…

[JimL]

2: actually wanted the actual data, not the structure

no index: yep, I’d solve it like this somewhere at the beginning of the file:

[php]$formCat = isset($_POST[‘formCat’]) ? $_POST[‘formCat’] : null;[/php]

then use $formCat through the rest of the file

[digicoder]

The data in the DB for the item_Url field looks like http://websiteaddress.com

http://imgur.com/tfq9AZo

Additionally, as you can see in the pic, each entry in this table has a foreign key field named “item_Cat_Id”. This field links each entry to another table, named “tcategories”. So that if “item_Cat_Id = 1”, then this entry would belong to Category #1.
When the user selects, “All in one” in the form (currently category #1), for example, php would then display all entries in titems table with foreign key set as 1.

I hope this sheds some light and not complicate it more instead.

[digicoder]

Jim,

your question on how the data looks in the DB helped me fix the URL issue I was having. Basically what happened is that the data was already stored as URL in the DB. When outputted via PHP, an additional href was used. After changing the data in DB to regular text, the following statement is now doing its job properly:

[php]
echo 'Link: ’ . ‘<a href = "’ . $row[‘item_Url’] . ‘">’ . $row[‘item_Url’] . ‘

’;
[/php]

So point #3 is now fixed, thank you! I will reply to the remaining things in the next post.

[digicoder]

Ah! Spoke too soon sorry. Data outputted by the PHP statement looks like this:

Link: http://www.dogedraws.com

But the link takes you to: http://localhost/"http://www.dogedraws.com"

Pf, I am a noob.

[digicoder]

I have a few categories. If I used your suggested lines of code (which I’d love to), how does the system know which category has the user chosen (1, 2, 3, 4, 5, etc…), so that it can pass it as a parameter for the prepared statement?

[php]
$sth = $dbh->prepare(‘SELECT * FROM titems WHERE titems.item_Title = ?’);
$sth->bindParam(1, $_POST[‘formCat’], PDO::PARAM_STR);
[/php]

[JimL]

You had a setup where you set up if cat title = something then do query with cat id number. Just add that logic to the query. That means you will have to have some table in the database connecting category title and category id.

Or you can just subit the category id. The category select box xan show ritles while the submitted value is just the id

[digicoder]

I believe you meant something like the code below. It works great, thank you. The only problem is that if I throw in there the $formCat variable you suggested earlier (commented below in the php section), that statement sets the variable to null (I think) and then the ‘:number’ in the query will always be ‘null’. If I comment the $formCat, everything works fine.

		<form action="companies.php" method="post">
		   <p style="font-family: Verdana; font-size: 12px; margin-left: 55px;">Choose a category: <select name="formCat">
		   <option value="">Select...</option>
                   <option value="">All Categories</option>
		   <option value="1">All in one</option>
		   <option value="2">Art</option>
		   <option value="3">Books</option>
		   <option value="4">Gift Cards</option>
		   <option value="5">Jewelry, Accessories & Apparel</option>
		   <option value="6">Food</option>
		   <option value="7">Music</option>
                   </select>
                   <input type="submit" name="formSubmit" value="Submit" />
                   </p>
		</form>

[php]
<?php
include_once “dbconn.php”;

    //$formCat = isset($_POST['formCat']) ? $_POST['formCat'] : null; 
    $sth = $db->prepare('SELECT * FROM titems WHERE titems.item_Cat_Id = :number');
    $sth->bindParam(':number', $_POST['formCat'], PDO::PARAM_STR);
    $sth->execute();
    if ($_POST['formCat']) {
        echo "<table id = 'listingtable'>";
        echo "<thead><tr><th width='20%'>Company Name</th><th>Description</th><th>Link</th></tr></thead>";
        echo "<tbody>";
        while($row = $sth->fetch()) {
            echo "<tr><td>" . $row['item_Name'] . "</td>" . "<td>" . $row['item_Desc'] . "</td>" . "<td>" . '<a href = \"' . $row['item_Url'] . '">' . $row['item_Url'] . '</a></td>';
            echo "</tr>";
        }
    echo "</tbody></table>";
    }
    ?>

[/php]

[JimL]

Just wrap the rest in an

[php]if ($formCat) {
// query code here
}[/php]

No need to run the querystuff if nl category is selected

[digicoder]

Ah! Awesome, thank you