HTML Website help

**Aylee** · 2014-10-30 11:57

new project is in lol. And its to be able to search by console, publish date, rarity, ext lol. In theory I could just edit the sql based on which was searched by right? (havent actually started working on it yet, wont have time until Sunday with work sadly just trying to get an idea of how to go about it)

**Corrderio** · 2014-10-30 14:42

So basically, you need to expand on your search so it can match multiple parameters?

**Aylee** · 2014-10-30 22:41

Basically.

**Corrderio** · 2014-10-30 23:25

Not too bad, but going to be messy.

**The Stig** · 2014-10-31 07:34

Originally Posted by Corrderio

Not too bad, but going to be messy.

Our worst fear has been realised!!!

**Corrderio** · 2014-10-31 09:54

I got an idea in my head, but I need to test it out before I can post it. I'll try to get back to you tomorrow since I got plans tonight that may involve me being buzzed.

**Aylee** · 2014-11-02 21:14

Atm just have to be able to search by all the different fields, but next week will be a combo. Figured for this week I could do a bunch of if statements basicly, but would be pointless since have to redo it all next week

**Corrderio** · 2014-11-03 08:49

Shit I forgot about this. Sorry between XIV and me feeling like shit (Fever and possibly pink eye) this skipped my mind.

**Aylee** · 2014-11-03 09:19

Originally Posted by Corrderio

Shit I forgot about this. Sorry between XIV and me feeling like shit (Fever and possibly pink eye) this skipped my mind.

Lol not a problem man, I dont expect any of you to do anything if you dont want to. your life way more important than helping me, but i appreciate all the help you guys have been giving me.

I figure I can do the search thing like this to get each individual one working, but as for combinations of them I have 0 clue

Code:

        if(!empty($_POST['title'])) 
        { 
            $sql .= ' WHERE title LIKE :title'; 
            $param = array(':title' => "%{$_POST['title']}%");
        }
        if(!empty($_POST['console'])) 
        { 
            $sql .= ' WHERE console LIKE :console'; 
            $param = array(':console' => "%{$_POST['console']}%");
        }
        if(!empty($_POST['genre'])) 
        { 
            $sql .= ' WHERE genre LIKE :gerne'; 
            $param = array(':genre' => "%{$_POST['genre']}%");
        }

which that way works... its a very stuipid and wrong work around but it works (well after i remove the likes for stuff like console).

Maybe if the field is empty post * if has conenot post the value? (For the combined search

**Corrderio** · 2014-11-03 12:42

Yeah, that's how I was going to do it, but before the if statements I was going to instantiate a blank string ("") before the ifs then nest an if statement into each if statement like so:

Code:

        $sql = "";

        if(!empty($_POST['title'])) 
        { 
            if($sql = "")
            {
                 $sql .= ' WHERE title LIKE :title'; 
            }
            else
            {
                 $sql .= ' AND WHERE title LIKE :title'; 
            }

            $param = array(':title' => "%{$_POST['title']}%");
     }

I'm not quite where where param was from (Once again, I need to brush up on my PHP/SQL interaction), but the general idea was to check what's empty and what isn't in your SQL string. If there is something already there you want to add AND to it so you can run your query against multiple columns. For example, if you wanted to search for a game and it's year (Let's take Mega Man 2 which was released in 1988), the query would look like:

Code:

WHERE title LIKE 'Mega Man 2' AND year = 1988

**Aylee** · 2014-11-03 13:06

Well I was doing it like the following, problem is any of the searches before the last one listed wont work. In this example, I cant search by title or console but genre will work. If i delete the genra one then console will work. also tried nested like you suggested and that didnt work either

Code:

        if(!empty($_POST['title'])) 
        { 
            $sql .= ' WHERE title LIKE :title'; 
            $param = array(':title' => "%{$_POST['title']}%");
        }
        if(!empty($_POST['console'])) 
        { 
            $sql .= ' WHERE console = :console'; 
            $param = array(':console' => "{$_POST["console"]}");
        }
        if(!empty($_POST['genre'])) 
        { 
            $sql .= ' WHERE genre = :genre'; 
            $param = array(':genre' => "{$_POST["genre"]}");
        }

Also I am having trouble saving the image path for some reason. It wont save the name to the database and im not sure. Any one have some ideas?

Code:

					
					Upload Image: <input type="file" name="uploadFile"><br>
								
					<input type = "submit" value = "Submit" name = "gameSubmit" style="width: 100px; height: 50px;" />
						<?php
						if(!empty($_FILES)) {
							$target_dir = "images/";
							$target_dir = $target_dir . basename( $_FILES["uploadFile"]["name"]);
							$uploadOk=1;

							// Check if file already exists
							if (file_exists($target_dir . $_FILES["uploadFile"]["name"])) {
								echo "Sorry, file already exists.";
								$uploadOk = 0;
							}

							else { 
								if (move_uploaded_file($_FILES["uploadFile"]["tmp_name"], $target_dir)) {
									echo "The file ". basename( $_FILES["uploadFile"]["name"]). " has been uploaded.";
								} else {
									echo "Sorry, there was an error uploading your file.";
								}
							}
						}
					?>
					
					<!-- PHP statments to push New game data to the database -->
					<?php
						if (empty($_POST)) {
							 //No form data found, moving on    
						}
						else {
							//Creating the string for the SQL statement with named placeholders
							$sql = "Insert Into Games VALUES (:title, :console, :genre, :publisher, :releaseYear, :rarity, :details, :image)";

							 //Prepare the query
							$sth = $dbh->prepare($sql);
							
							//Create array with the named place holders as keys and form data as values
							$data = array(
								':title' => $_POST["title"],
								':console' => $_POST["console"],
								':genre' => $_POST["genre"],
								':publisher' => $_POST["publisher"],
								':releaseYear' => $_POST["releaseYear"],
								':rarity' => $_POST["rarity"],
								':details' => $_POST["details"],
								':image' => $_POST["uploadFile"],
							);
							
							//Execute the query with the place holders populated by the data from the array
							$sth->execute($data);        
						}
					?>

**Aevis** · 2014-11-04 05:42

Because $_POST["uploadFile"] doesn't contain anything. Form fields of type "file" are submitted through the global $_FILES. Look at the first half of your code where you upload the image. Depending on what you want to save, it's either $_FILES["uploadFile"]["name"] or the $target_dir in combination with that. Use a browser capable of showing POST/GET data or echo it yourself to get more insight:

For variables

Code:

<?php echo $_POST["uploadFile"]["name"]; ?>

For arrays/objects

Code:

<?php var_dump($_POST["uploadFile"]); ?>

Search

You're both going into the right direction, but don't repeat yourself (DRY principle). Having to deal with an unknown amount of data input screams for iteration. While $_POST already is an array you can iterate over, you should put all the search related variables into their own array. Otherwise you get ALL $_POST data and have to filter out unnecessary stuff. To accomplish that, go into your form code and change the related input names, e.g.

Code:

<input type="Text" name="title">

becomes

Code:

<input type="Text" name="game[title]">

That means, all your game search related POST data is now gathered in the $_POST["game"] array and you can iterate over that:

Code:

    if (empty($_POST)) {
         //No form data found, moving on    
    }
    else {
        
        //Reset counter
        $i = 0;
        
        //Column whitelist
        $columns = array('title', 'console', 'genre', 'publisher', 'releaseYear', 'rarity');
        
        //Basic SQL
        $sql = "SELECT * FROM Games";
        
        //Loop over the $_POST["game"] array
        foreach($_POST["game"] as $key => $value)
        {
            //Check if key value is a proper column name
            if(in_array($key, $columns))
            {
                //Only add condition if it's not empty
                if(!empty($value))
                {
                    //If at first loop, use WHERE, otherwise AND
                    if($i == 0) { $sql .= " WHERE "; }
                    else { $sql .= " AND "; }
                    
                    ////Build rest of the SQL and the placeholders array
                    $sql .= "$key LIKE ?";
                    $param[] = "%{$value}%";

                    //Increase counter
                    $i++;
                }
            }
            else { exit("Error! $key is not in whitelist"); }
        }
        
        //Prepare the query
        $sth = $dbh->prepare($sql);
        
        //Execute the query with the placeholders populated by the data from the array
        $sth->execute($param);

        // Fetch the results // 
        $rowset = $sth->fetchAll(PDO::FETCH_ASSOC);

        // Show the results //
        echo json_encode($rowset);
    }

Two things:

Column Whitelist

We use the $_POST["game"]["title"] key value (title) as column name in the query. Although the user can't directly change that value through your page, it's easy to send any kind of $_POST data manually. PDO unfortunately doesn't have any method to automatically sanitize column names, unlike it does for the input data through placeholders. The easiest way to be safe is to compare it against a whitelist and only proceed if it matches.

Regular placeholders

We use regular ones here

Code:

SELECT * FROM Games WHERE title LIKE ? AND console LIKE ? AND genre LIKE ?

$param = array('dudu', 'dada', 'didi');

instead of named ones

Code:

SELECT * FROM Games WHERE title LIKE :title AND console LIKE :console AND genre LIKE :genre

$param = array(':title' => 'dudu', ':console' => 'dada', 'genre' => 'didi');

We don't need named ones because we're iterating over key/value pairs. So the key value (e.g. title) used for the column name in the query statement is already tied to the corresponding value (e.g. super mario).

**Aylee** · 2014-11-04 11:12

Originally Posted by Aevis

Because $_POST["uploadFile"] doesn't contain anything. Form fields of type "file" are submitted through the global $_FILES. Look at the first half of your code where you upload the image. Depending on what you want to save, it's either $_FILES["uploadFile"]["name"] or the $target_dir in combination with that. Use a browser capable of showing POST/GET data or echo it yourself to get more insight:

For variables

Code:

<?php echo $_POST["uploadFile"]["name"]; ?>

For arrays/objects

Code:

<?php var_dump($_POST["uploadFile"]); ?>

Search

You're both going into the right direction, but don't repeat yourself (DRY principle). Having to deal with an unknown amount of data input screams for iteration. While $_POST already is an array you can iterate over, you should put all the search related variables into their own array. Otherwise you get ALL $_POST data and have to filter out unnecessary stuff. To accomplish that, go into your form code and change the related input names, e.g.

Code:

<input type="Text" name="title">

becomes

Code:

<input type="Text" name="game[title]">

That means, all your game search related POST data is now gathered in the $_POST["game"] array and you can iterate over that:

Code:

    if (empty($_POST)) {
         //No form data found, moving on    
    }
    else {
        
        //Reset counter
        $i = 0;
        
        //Column whitelist
        $columns = array('title', 'console', 'genre', 'publisher', 'releaseYear', 'rarity');
        
        //Basic SQL
        $sql = "SELECT * FROM Games";
        
        //Loop over the $_POST["game"] array
        foreach($_POST["game"] as $key => $value)
        {
            //Check if key value is a proper column name
            if(in_array($key, $columns))
            {
                //Only add condition if it's not empty
                if(!empty($value))
                {
                    //If at first loop, use WHERE, otherwise AND
                    if($i == 0) { $sql .= " WHERE "; }
                    else { $sql .= " AND "; }
                    
                    ////Build rest of the SQL and the placeholders array
                    $sql .= "$key LIKE ?";
                    $param[] = "%{$value}%";

                    //Increase counter
                    $i++;
                }
            }
            else { exit("Error! $key is not in whitelist"); }
        }
        
        //Prepare the query
        $sth = $dbh->prepare($sql);
        
        //Execute the query with the placeholders populated by the data from the array
        $sth->execute($param);

        // Fetch the results // 
        $rowset = $sth->fetchAll(PDO::FETCH_ASSOC);

        // Show the results //
        echo json_encode($rowset);
    }

Two things:

Column Whitelist

We use the $_POST["game"]["title"] key value (title) as column name in the query. Although the user can't directly change that value through your page, it's easy to send any kind of $_POST data manually. PDO unfortunately doesn't have any method to automatically sanitize column names, unlike it does for the input data through placeholders. The easiest way to be safe is to compare it against a whitelist and only proceed if it matches.

Regular placeholders

We use regular ones here

Code:

SELECT * FROM Games WHERE title LIKE ? AND console LIKE ? AND genre LIKE ?

$param = array('dudu', 'dada', 'didi');

instead of named ones

Code:

SELECT * FROM Games WHERE title LIKE :title AND console LIKE :console AND genre LIKE :genre

$param = array(':title' => 'dudu', ':console' => 'dada', 'genre' => 'didi');

We don't need named ones because we're iterating over key/value pairs. So the key value (e.g. title) used for the column name in the query statement is already tied to the corresponding value (e.g. super mario).

So this is what I have so far.

Code:

<html>

    <?php
                 
        // Specify your sqlite database name and path
        $dir = 'sqlite:GameCollection';
             
        // Instantiate PDO connection object and failure msg 
        $dbh = new PDO($dir) or die('cannot open database');
                 
        // Define your SQL statement, myTable = table name in your DB 
        $sql = 'SELECT rowid, title, console, genre, publisher, releaseyear, rarity, details, image FROM Games';
        
        // If a title is submitted, add a condition to the query and
        // create array with the named place holder as key and form data as value 
    if (empty($_POST)) {
         //No form data found, moving on    
    }
    else {       
        //Reset counter
        $i = 0;
        
        //Column whitelist
        $columns = array('title', 'console', 'genre', 'publisher', 'releaseYear', 'rarity');
        
        //Basic SQL
        $sql = "SELECT * FROM Games";
        
        //Loop over the $_POST["game"] array
        foreach($_POST["game"] as $key => $value)
        {
            //Check if key value is a proper column name
            if(in_array($key, $columns))
            {
                //Only add condition if it's not empty
                if(!empty($value))
                {
                    //If at first loop, use WHERE, otherwise AND
                    if($i == 0) { $sql .= " WHERE "; }
                    else { $sql .= " AND "; }
                    
                    ////Build rest of the SQL and the place holders array
                    $sql .= "$key LIKE ?";
                    $param[] = "%{$value}%";

                    //Increase counter
                    $i++;
                }
            }
            else { exit("Error! $key is not in whitelist"); }
        }
        
        //Prepare the query
        $sth = $dbh->prepare($sql);
        
        //Execute the query with the placeholders populated by the data from the array
        $sth->execute($param);

        // Fetch the results // 
        $rowset = $sth->fetchAll(PDO::FETCH_ASSOC);

        // Show the results //
        echo json_encode($rowset);
    }
    ?>
                   
    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width">
        <title>NES Collection</title>
        <link id="pagestyle" href="style.css" rel="stylesheet" type="text/css">
        <script src="http://code.jquery.com/jquery-1.11.1.min.js"></script>
        <script src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>

    <body>

        <div id="header">
            <h1>Retro Game Collection</h1><br><br><br>
            <a href = "database.php"> Home</a>
            <a href = "Search.php">Search</a>
        </div>
        
        <div id ="searchBody">
            <form action = "Search.php" method = "post">
                <h3> Search By: </h3> <br> 
				
					Title: <input type="Text" name="game[title]"> <br>
													
					<!-- Drop down box for the console -->
					Console:
					<select name = "game[console]">
						<option value =""> </option>
						<option value ="Nintendo"> Nintendo </option>
						<option value ="Super Nintendo"> Super Nintendo </option>
						<option value ="Nintendo 64"> Nintendo 64 </option>
						<option value ="Game Cube"> Game Cube </option>
						<Option value ="Playstation"> Playstation </option>
						<option Value ="Playstation2"> Playstaiton 2 </option>
						<option value ="Xbox"> Xbox </option>
					</select><br>
					
					<!-- Drop down box for the Genre -->
					Genre:
					<select name= "game[genre]">
						<option value ="">  </option>
						<option value="Action"> Action </option>
						<option value="Beatup"> Beat'em Up </option>
						<option value="Platform"> Platformer </option>
						<option value="shooter"> Shooter </option>
						<option value="Adventure"> Adventure </option>
						<option value="rpg"> Role-Playing </option>
						<option value="Sim"> Simulation </option>
						<option value="strategy"> Strategy </option>
						<option value="Sports"> Sports </option>
					</select><br>	
					
					<!-- Drop down box for the publisher -->
					Publisher:
					<select name = "game[publisher]">
						<option value ="">  </option>				
						<option value="Aklaim"> Aklaim </option>
						<option value="Capcom">Capcom </option>
						<option value="EA"> EA </option>
						<option value="KOEI">KOEI</option>
						<option value="Konami"> Konami </option>
						<option value="Nintendo"> Nintendo </option>
						<option value ="Rare">Rare</option>
						<option value="Snk">SNK</option>
						<option value="SunSoft">SunSoft </option>
						<option value="Square Enix"> Square Enix </option>
						<option value="Tecmo"> Tecmo </option>
						<option value="TradeWest"> TradeWest </option>
						<option value="Broderbund"> Broderbund </option>
					</select><br>
					
					<!-- Text field for the release year -->
					Release Year: <input type="Text" name="game[releaseYear]"><br>
					
					<!-- Drop down box for the Rarity option -->
					Rarity:
					<select name= "game[rarity]">
						<option value ="">  </option>
						<option value="Common"> Common </option>
						<option value="Uncommon"> Uncommon </option>
						<option value="Rare"> Rare </option>
						<option value="veryRare"> Very Rare </option>
						<option value="SuperRare"> Super Rare </option>
						<option value="Ultrarare"> Ultra Rare </option>
					</select><br>
				
                <input type = "submit" value = "Submit" name = "searchSubmit" style="width: 100px; height: 20px;" />
            </form>
        </div>
        
        <div id="LowerBody">
            <table border ="1" width: "100%">
                <tr>
                    <th>Title</th>
                    <th>Console</th> 
                    <th>Genre</th>
                    <th>Publisher</th>
                    <th>Release Year</th>
                    <th>Rarity</th>
                    <th>Details</th>
                    <th>img Path</th>
                </tr>
                <tr>
                
                <?php foreach($rowset as $row): ?>
                    <tr>
                        <td>
                            <a href="gameDetails.php?rowid=<?php echo $row['rowid']; ?>">
                                <?php echo $row['title']; ?>
                            </a>
                        </td>
                        <td><?php echo $row['console']; ?></td>
                        <td><?php echo $row['genre']; ?></td>
                        <td><?php echo $row['publisher']; ?></td>
                        <td><?php echo $row['releaseYear']; ?></td>
                        <td><?php echo $row['rarity']; ?></td>
                        <td><?php echo $row['details']; ?></td>
                        <td><?php echo $row['image']; ?></td>
                    </tr>
                <?php endforeach; ?>
            </table>            
        </div>
            

    </body>
    
</html>

This is kinda confusing me though

Code:

                    ////Build rest of the SQL and the place holders array
                    $sql .= "$key LIKE ?";
                    $param[] = "%{$value}%";

i'm assuing $key is representing the current field its looking at (title, console, genre, ext)?

so would it be something like:

Code:

                    ////Build rest of the SQL and the place holders array
                    $sql .= "$key LIKE game[console]";
                    $param[] = "%{$value}%";

**Aevis** · 2014-11-04 12:48

A foreach($dudu as $key => $value) syntax will additionally assign the current element's key to the $key variable, instead of just the value (http://php.net/manual/de/control-structures.foreach.php). Say you have following array

Code:

array(
    "title" => "mario",
    "console" => "SNES",
)

On the first iteration, $key variable will hold "title" and the $value variable "mario". On the second, $key will hold "console" and $value "SNES". That way it's possible to build the sql query dynamically depending on what was actually submitted. At the end you want to build a query with regular (unnamed) placeholders and an array ($param), which contains the values from the user submitted data. Like my regular placeholders example. For a better understanding, you can add the following (or similar) echo() after the $param[] = "%{$value}%"; line.

Code:

echo "Key: $key and value: $value cycle. The query so far is: $sql <br> and the param array looks like this: " . var_dump($param) . "<br>";

**Corrderio** · 2014-11-04 13:23

Aevis. Resources for me to study, prz!

**Aevis** · 2014-11-04 13:39

What are you looking for?

**Aylee** · 2014-11-04 13:43

Originally Posted by Aevis

A foreach($dudu as $key => $value) syntax will additionally assign the current element's key to the $key variable, instead of just the value (http://php.net/manual/de/control-structures.foreach.php). Say you have following array

Code:

array(
    "title" => "mario",
    "console" => "SNES",
)

On the first iteration, $key variable will hold "title" and the $value variable "mario". On the second, $key will hold "console" and $value "SNES". That way it's possible to build the sql query dynamically depending on what was actually submitted. At the end you want to build a query with regular (unnamed) placeholders and an array ($param), which contains the values from the user submitted data. Like my regular placeholders example. For a better understanding, you can add the following (or similar) echo() after the $param[] = "%{$value}%"; line.

Code:

echo "Key: $key and value: $value cycle. The query so far is: $sql <br> and the param array looks like this: " . var_dump($param) . "<br>";

Oh ok now I understand I think. So I'm not actually changing anything in that section right? But still kinda confused a little. So $ key in my example would hold title the first time through the loop then console, ext right? One problem I see Is say I choose console as part of the search. If we use "like" it would include super nintendo, nintendo 64, and nintendo instead of just nintendo

**Aevis** · 2014-11-04 13:59

That's correct. Your task now is adjust it to the needs of your search. As example, if you only want to use the LIKE operator for the title but for everything else it should use "=". My approach would be to use

Code:

$sql .= "$key LIKE ?";
$param[] = "%{$value}%";

only if $key equals title, else

Code:

$sql .= "$key = ?";
$param[] = $value;

That way you have a LIKE and a = (equal) case and can assign them by putting the desired form input variable name into the IF condition.

**Aylee** · 2014-11-04 14:53

I'm at work so I can't type well or make it look neat, so what your saying is basically this:

If ($key = title){
$sql .= "$ key like ?";
$param[] = "%{$value}%";
}
else {
$sql .= "$key = ?";
$param[] = $value;
}

What is the ? Is that like a real operator? Or does it mean to fill it in with each of the fields (aka copy paste it a bunch and put :console or :title in that spot

**Aevis** · 2014-11-04 15:14

It's a positional placeholder (I wrongly called it regular placholder) instead of a named placeholder: http://www.phpeveryday.com/articles/...ders-P551.html

Thread: HTML Website help

Thread Tools

Similar Threads

Simple HTML issue with my website, need help.

HTML / VBS / Java Script Gurus -- Help Needed!

Latest Threads

Up & Coming Threads

Hottest Threads