XI Wiki
Ah ok. Tried what you said. First of all I get a error when trying to display the entire database prior to a search
At first i thought this was because $rowset got deleted when doing the new search code, but I see that its still there just presented in a different way, so not really sure why its doing that.
Also when I do a search i get all the wierd text at the top. Havent started trying to figure out this problem but about to start working on it now.
Third, tried adding the new if/else statment to change it from like to = but its not workingjust getting 0 results with this code. Fairly sure its because of syntax error not knowing how to call table. Tried 'table' and ':table' . Again working on this after i clean it up just posting my issues as i get them.
Also just noticed that after I search for something and click a result its not loading the informaiton. its taking me to a page named:Code:////Build rest of the SQL and the place holders array if($key = ':title'){ $sql .= "$key LIKE ?"; $param[] = "%{$value}%"; } else{ $sql .= "$key = ?"; $parm[] = "$value"; }
http://localhost/neslist-css/slider/...3E%3Cbr%20/%3E
Again slowly working on all these just slow progress lmao
Also guess it might help if i post the full code, I left out the if/else in the building sql place holder array part for testing purposes
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>
- Navigation
+ Reply to Thread
Results 141 to 160 of 165
Thread: HTML Website help
-
2014-11-04 21:28 #141Salvage Bans
- Join Date
- Jun 2011
- Posts
- 927
- BG Level
- 5
- FFXI Server
- Sylph
-
2014-11-05 02:10 #142wotg torrent kitty :3
- Join Date
- Jun 2007
- Posts
- 1,643
- BG Level
- 6
The main issue is, that the query execution part is inside the $_POST conditional. So if there is no $_POST data, it won't be executed and $rowset doesn't exist.
The wierd text comes from the
line. Remove that, it's not needed because you show all your results through theCode:// Show the results // echo json_encode($rowset);
foreach($rowset as $row) loop.
Your if/else doesn't work, because a single equal sign assigns the value on the right side to the variable on the left in php. A double equal sign compares it (tripple equals would additionally compare the type). Change
toCode:if($key = ':title')
Clicking doesn't work because $rowid isn't set. Reason is, that the $sql query inside the $_POST conditional is SELECT * FROM Games. A wildcard query won't receive the rowid, you have to call it explicitly. The whole line is redundant in your site anyway, because the basic query is already built before the $_POST conditional.Code:if($key == 'title')
Fully adjusted site code
I added one additional lineCode:<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'); // Set PDO error handling to throw exceptions $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Define your SQL statement, myTable = table name in your DB $sql = 'SELECT rowid, title, console, genre, publisher, releaseyear, rarity, details, image FROM Games'; // If there is no POST data, create empty $param array if (empty($_POST)) { $param = array(); } // Append POST data dynamically to the query else { //Reset counter $i = 0; //Column whitelist $columns = array('title', 'console', 'genre', 'publisher', 'releaseYear', 'rarity'); //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); ?> <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>
By default, PDO execute() doesn't throw exceptions. So if your query is wrong, you won't know because the error is thrown when it tries to use the execute() method on the $sth object, which isn't an object becauseCode:// Set PDO error handling to throw exceptions $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
didn't work, due to the invalid $sql query. To test this yourself, change your $sql to "SELECT dudu FROM Games"Code:$sth = $dbh->prepare($sql);
With the error handling set to throw exceptions, you'll see the actual problemCode:Fatal error: Call to a member function execute() on a non-object
Code:Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1 no such column: dudu'
-
2014-11-05 07:57 #143Salvage Bans
- Join Date
- Jun 2011
- Posts
- 927
- BG Level
- 5
- FFXI Server
- Sylph
You sir are amazing. I have a few more things I need to get done on it but I think I can figure out some or most of it.
1) Convert drop downs check boxes and be able to select multiple or all objects from list
2) with publisher date use a calendar and be able to show all dates between the selected dates
3) search the details for key words
I've been working on the keywords part for #3 but as soon as I add the details text area it completely breaks the entire search for some reason. (added it to the white list, no errors, but when searching for say "nintendo" in the console field it returns 0 results.)
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'); // Set PDO error handling to throw exceptions $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Define your SQL statement, myTable = table name in your DB $sql = 'SELECT rowid, title, console, genre, publisher, releaseyear, rarity, details, image FROM Games'; // If there is no POST data, create empty $param array if (empty($_POST)) { $param = array(); } // Append POST data dynamically to the query else { //Reset counter $i = 0; //Column whitelist $columns = array('title', 'console', 'genre', 'publisher', 'dateRange', 'releaseYear', 'rarity', 'details'); //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 if($key == 'title' || 'details'){ $sql .= "$key LIKE ?"; $param[] = "%{$value}%"; } else { $sql .= "$key = ?"; $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 place holders populated by the data from the array $sth->execute($param); // Fetch the results // $rowset = $sth->fetchAll(PDO::FETCH_ASSOC); ?> <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 before/after --> Release Year: <select name = "game[dateRange]"> <option value = ""> </option> <option value = "before"> Before </option> <option value = "after"> After </option> </select> <!-- Text field for the 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> Details:<br> <textarea name = "game[details]" rows ="4" cols="50"> </textarea><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>
-
2014-11-05 12:57 #144Falcom is better than SE. Change my mind.
- Join Date
- Jun 2006
- Posts
- 17,291
- BG Level
- 9
I guess maybe some standard stuff to get started. Most of my PHP background came from Dreamweaver (Which I consider an abomination for any site development) and a book I got on my Kindle.
Originally Posted by Aevis
-
2014-11-06 01:42 #145wotg torrent kitty :3
- Join Date
- Jun 2007
- Posts
- 1,643
- BG Level
- 6
Best way to learn a new language IMO is an actual exercise and google-fu. The w3 site covers the very basics decently: http://www.w3schools.com/php/
However, if you already have experience in OOP (think you mentioned java/c#), I wouldn't spend too much time with procedural PHP like we're doing here. It's absolutely required to understand procedural code before moving to OOP, but I imagine it's rather akward to go from c# to procedural PHP.
This looks like a good tutorial for procedural vs OOP and it uses Googles PHP API client to manipulate your own google calendar as exercise: http://code.tutsplus.com/tutorials/f...php--net-32853
A recommendation: Notepad++ is a great editor for quick development stuff, I used it for everything here. For a bigger project with dependencies I'd use a real IDE like Netbeans, Eclipse, Aptana, PHPStorm etc.
-
2014-11-06 09:17 #146Falcom is better than SE. Change my mind.
- Join Date
- Jun 2006
- Posts
- 17,291
- BG Level
- 9
I know a little bit of procedural (Assuming True BASIC is considered procedural). But rusty on it since I havne't done that since high school, which was 8+ years ago.
But yeah, I guess my foggier part is executing SQL like you have been (That's where lolDreamWeaver came in for me), more about transferring data from page to page, and external libraries or whatever it's called in PHP.
-
2014-11-09 20:15 #147Salvage Bans
- Join Date
- Jun 2011
- Posts
- 927
- BG Level
- 5
- FFXI Server
- Sylph
So professor wants me to change the drop down boxes into check boxes and be able to select multiple ones.... I have it converted over to check boxes, but when searching by it it only searches by the last option checked. How do i go about searching all the options checked? I would think that I could check to see if more than 1 option is selected and if they are add it into an or statement but not really sure how the syntax for that would look.
Maybe something like
my check boxes look like the following:Code:if($key == 'console'){ $sql .= "$key || ?"; }
Also wanted to change the release year input field to a date picker (which i've added kinda). Where basically I can click the release year field and it will pop up a calander (works) and select a date, then click the next field repeat, and then it gives me all games with a release year between those dates. The calender pops up on the first one but not the second. Not sure wtf the problem is.Code:<!-- Drop down box for the console --> <b>Console</b><br> <input type = "checkbox" name = "game[console]" value = "Nintendo"> Nintendo <input type = "checkbox" name = "game[console]" value = "Super Nintendo"> Super Nintendo<br> <input type = "checkbox" name = "game[console]" value = "Nintendo 64">Nintendo 64 <input type = "checkbox" name = "game[console]" value = "Game Cube"> Game Cube<br> <input type = "checkbox" name = "game[console]" value = "Playstation"> Playstation <input type = "checkbox" name = "game[console]" value = "Playstation2"> Playstation2<br> <input type = "checkbox" name = "game[console]" value = "Xbox"> Xbox<br>
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'); // Set PDO error handling to throw exceptions $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Define your SQL statement, myTable = table name in your DB $sql = 'SELECT rowid, title, console, genre, publisher, releaseyear, rarity, details, image FROM Games'; // If there is no POST data, create empty $param array if (empty($_POST)) { $param = array(); } // Append POST data dynamically to the query else { //Reset counter $i = 0; //Column whitelist $columns = array('title', 'console', 'genre', 'publisher', 'dateRange', 'releaseYear', 'rarity',); //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 if($key == 'title'){ $sql .= "$key LIKE ?"; $param[] = "%{$value}%"; } else { $sql .= "$key = ?"; $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 place holders populated by the data from the array $sth->execute($param); // Fetch the results // $rowset = $sth->fetchAll(PDO::FETCH_ASSOC); ?> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width"> <title>NES Collection</title> <title>jQuery UI Datepicker - Default functionality</title> <link rel="stylesheet" href="//code.jquery.com/ui/1.11.2/themes/smoothness/jquery-ui.css"> <script src="//code.jquery.com/jquery-1.10.2.js"></script> <script src="//code.jquery.com/ui/1.11.2/jquery-ui.js"></script> <link rel="stylesheet" href="/resources/demos/style.css"> <script> $(function() { $( "#datepicker" ).datepicker(); }); </script> </head> <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 --> <b>Console</b><br> <input type = "checkbox" name = "game[console]" value = "Nintendo"> Nintendo <input type = "checkbox" name = "game[console]" value = "Super Nintendo"> Super Nintendo<br> <input type = "checkbox" name = "game[console]" value = "Nintendo 64">Nintendo 64 <input type = "checkbox" name = "game[console]" value = "Game Cube"> Game Cube<br> <input type = "checkbox" name = "game[console]" value = "Playstation"> Playstation <input type = "checkbox" name = "game[console]" value = "Playstation2"> Playstation2<br> <input type = "checkbox" name = "game[console]" value = "Xbox"> Xbox<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> Release Year: <input type="text" id="datepicker" name ="game[releaseYear]"> - <input type="text" id="datepicker" name ="game[releaseYear]> <!-- 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> <!-- Need to add in description field, currently breaks search --> <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>
-
2014-11-10 19:55 #148Salvage Bans
- Join Date
- Jun 2011
- Posts
- 927
- BG Level
- 5
- FFXI Server
- Sylph
I think i'm on the right path. Found a think talking about it saying use
broken but am i on the right track?Code:foreach($_POST['console'] as $selected){ $echo console; }
Full code:
UPDATE: Got the date picker working for year only status but not sure how to do the date rage for publisher either, but working on that atm since im at a standstill on the checkboxes. Plan for making release statment work is something like this: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'); // Set PDO error handling to throw exceptions $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Define your SQL statement, myTable = table name in your DB $sql = 'SELECT rowid, title, console, genre, publisher, releaseyear, rarity, details, image FROM Games'; // If there is no POST data, create empty $param array if (empty($_POST)) { $param = array(); } // Append POST data dynamically to the query else { //Reset counter $i = 0; //Column whitelist $columns = array('title', 'console', 'genre', 'publisher', 'dateRange', 'releaseYear', 'rarity',); //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 if($key == 'title'){ $sql .= "$key LIKE ?"; $param[] = "%{$value}%"; } else { $sql .= "$key = ?"; $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 place holders populated by the data from the array $sth->execute($param); // Fetch the results // $rowset = $sth->fetchAll(PDO::FETCH_ASSOC); ?> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width"> <title>NES Collection</title> <title>jQuery UI Datepicker - Default functionality</title> <link rel="stylesheet" href="//code.jquery.com/ui/1.11.2/themes/smoothness/jquery-ui.css"> <script src="//code.jquery.com/jquery-1.10.2.js"></script> <script src="//code.jquery.com/ui/1.11.2/jquery-ui.js"></script> <link rel="stylesheet" href="/resources/demos/style.css"> <link id="pagestyle" href="style.css" rel="stylesheet" type="text/css"> <script type="text/javascript"> $(function() { $("#startdate").datepicker({ dateFormat: "yy" }).val() $("#enddate").datepicker({ dateFormat: "yy" }).val() }); </script> </head> <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> <b>Title:</b><br> <input type="Text" name="game[title]"> <br> ___________________________________________<br> <!-- Drop down box for the console --> <b>Console</b><br> <input type = "checkbox" name = "game[console]" value = "Nintendo"> Nintendo <input type = "checkbox" name = "game[console]" value = "Super Nintendo"> Super Nintendo<br> <input type = "checkbox" name = "game[console]" value = "Nintendo 64">Nintendo 64 <input type = "checkbox" name = "game[console]" value = "Game Cube"> Game Cube<br> <input type = "checkbox" name = "game[console]" value = "Playstation"> Playstation <input type = "checkbox" name = "game[console]" value = "Playstation2"> Playstation2<br> <input type = "checkbox" name = "game[console]" value = "Xbox"> Xbox<br> ___________________________________________<br> <!-- Drop down box for the Genre --> <b>Genre: <br></b> <input type = "checkbox" name = "game[genre]" value = "Action">Action <input type = "checkbox" name = "game[genre]" value = "Beatup">Beat'em Up<br> <input type = "checkbox" name = "game[genre]" value = "Platform">Platformer <input type = "checkbox" name = "game[genre]" value = "Shooter">Shooter<br> <input type = "checkbox" name = "game[genre]" value = "Adventure">Adventure <input type = "checkbox" name = "game[genre]" value = "rpg">Role-Playing<br> <input type = "checkbox" name = "game[genre]" value = "Sim">Simulation <input type = "checkbox" name = "game[genre]" value = "Strategy">Strategy<br> <input type = "checkbox" name = "game[genre]" value = "Sports">Sports <br> <!-- Drop down box for the publisher --> ___________________________________________<br> <b>Publisher:<br></b> <input type = "checkbox" name = "game[publisher]" value = "Aklaim">Aklaim <input type = "checkbox" name = "game[publisher]" value = "Capcom">Capcom<br> <input type = "checkbox" name = "game[publisher]" value = "EA">EA <input type = "checkbox" name = "game[publisher]" value = "KOEI">KOEI<br> <input type = "checkbox" name = "game[publisher]" value = "Konami">Konami <input type = "checkbox" name = "game[publisher]" value = "Nintendo">Nintendo<br> <input type = "checkbox" name = "game[publisher]" value = "Rare">Rare <input type = "checkbox" name = "game[publisher]" value = "SNK">SNK<br> <input type = "checkbox" name = "game[publisher]" value = "SunSoft">SunSoft <input type = "checkbox" name = "game[publisher]" value = "Square Enix">Square Enix<br> <input type = "checkbox" name = "game[publisher]" value = "Tecmo">Tecmo <input type = "checkbox" name = "game[publisher]" value = "TradeWest">TradeWest<br> <input type = "checkbox" name = "game[publisher]" value = "Broderbund">Broderbund<br> ___________________________________________<br> <b>Start Date:</b> <input type="text" id="startdate" size="30"/> <b>End Date: </b> <input type="text" id="enddate" size="30"/> <!-- Drop down box for the Rarity option --> ___________________________________________<br> <b> Rarity: <br> </b> <input type = "checkbox" name = "game[rarity]" value = "Common">Common <input type = "checkbox" name = "game[rarity]" value = "Uncommon">Uncommon<br> <input type = "checkbox" name = "game[rarity]" value = "Rare">Rare <input type = "checkbox" name = "game[rarity]" value = "veryRare">Very Rare<br> <input type = "checkbox" name = "game[rarity]" value = "SuperRare">Super Rare <input type = "checkbox" name = "game[rarity]" value = "Ultrarare"> Ultra Rare<br> <!-- Need to add in description field, currently breaks search --> <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>
Code:else if($key == 'releaseYear'){ $sql .= "$Key <= releaseYearStart AND $key >= releaseYearEnd"; }
-
2014-11-11 03:19 #149wotg torrent kitty :3
- Join Date
- Jun 2007
- Posts
- 1,643
- BG Level
- 6
I'm at a RedHat training for the entire week, but I'll try to have a look tonight.
SQL syntax is "WHERE date BETWEEN start AND end". As long as your date field is a proper date format in the DB (DATE, DATETIME etc.) or integer (if you only ever save the year), it will be calculated correctly.
-
2014-11-11 07:55 #150Salvage Bans
- Join Date
- Jun 2011
- Posts
- 927
- BG Level
- 5
- FFXI Server
- Sylph
This is what I have for the date picker. it keeps this error: Notice: Undefined variable: param in E:\XAMPP\htdocs\NesList-CSS\Slider\Search.php on line 68. Cant figure out why its bitching about that variable when its been fine the entire time until i added the else if statement. Originally Posted by Aevis
Code:else if($key == 'releaseYear'){ $sql .= "releaseYear BETWEEN '{$_POST['releaseYearStart']}' AND '{$_POST['releaseYearEnd']}'"; }Current Full CodeCode:<b>Start Date:</b> <input type="text" id="startdate" name="releaseYearStart" size="30"/> <b>End Date: </b> <input type="text" id="enddate" name="releaseYearEnd" size="30"/><br>
Spoiler: show
-
2014-11-11 14:52 #151wotg torrent kitty :3
- Join Date
- Jun 2007
- Posts
- 1,643
- BG Level
- 6
param isn't set because with your current code, it isn't in the $key == 'releaseYear' conditional. It's only set if $key == 'title' or it's $key is neither 'title' nor 'releaseYear'. Even with param set, it won't work because the main loop goes through the $_POST["game"] array, which doesn't include $_POST["releaseYearStart"] and $_POST["releaseYearEnd"].
-
2014-11-11 15:12 #152Salvage Bans
- Join Date
- Jun 2011
- Posts
- 927
- BG Level
- 5
- FFXI Server
- Sylph
So could I set param to " " and add the two post to the array? Or is there a better way to do it? At work atm so can't actually try it myself lol. Originally Posted by Aevis
Nvm tried it and failed... Gonna try to fix that and check box multiple search in morning.
-
2014-11-12 07:17 #153Salvage Bans
- Join Date
- Jun 2011
- Posts
- 927
- BG Level
- 5
- FFXI Server
- Sylph
For the searching with checkboxes I've been trying to add the following statment... I think this is the right solution but unsure of the syntex for the foreach.
Now im kind of thinking that i'm overriding the data in the array because of the way i'm doing the checkboxes:Code:else if($key == 'console'){ $sql .= "$key = ?"; $param[] = "$value"; foreach(? as $value){ $.sql .= "OR ?"; } }
game is the array, so when i put console inside the array with a value of "nintendo" its being over ridden with "game Cube" when that one is selected maybe?Code:<input type = "checkbox" name = "game[console]" value = "Nintendo"> Nintendo <input type = "checkbox" name = "game[console]" value = "Super Nintendo"> Super Nintendo<br> <input type = "checkbox" name = "game[console]" value = "Nintendo 64">Nintendo 64 <input type = "checkbox" name = "game[console]" value = "Game Cube"> Game Cube<br> <input type = "checkbox" name = "game[console]" value = "Playstation"> Playstation <input type = "checkbox" name = "game[console]" value = "Playstation2"> Playstation2<br> <input type = "checkbox" name = "game[console]" value = "Xbox"> Xbox<br>
-
2014-11-12 11:54 #154wotg torrent kitty :3
- Join Date
- Jun 2007
- Posts
- 1,643
- BG Level
- 6
Yes. To get multiple checkboxes to work, you either have to use an array without giving an index (game[], but doesn't work with our php logic) or pass a multidimensional array by hard coding the indexes for the game[console] array
The $value now becomes an array which you can foreach overCode:<input type = "checkbox" name = "game[console][0]" value = "Nintendo"> Nintendo <input type = "checkbox" name = "game[console][1]" value = "Super Nintendo"> Super Nintendo<br> <input type = "checkbox" name = "game[console][2]" value = "Nintendo 64">Nintendo 64 <input type = "checkbox" name = "game[console][3]" value = "Game Cube"> Game Cube<br> <input type = "checkbox" name = "game[console][4]" value = "Playstation"> Playstation <input type = "checkbox" name = "game[console][5]" value = "Playstation2"> Playstation2<br> <input type = "checkbox" name = "game[console][6]" value = "Xbox"> Xbox<br>
Code:[0] => Nintendo [1] => Super Nintendo [2] => Nintendo 64 etc.
The $param line looks a bit different. Since $value is an array in this case and not a string, you could writeCode:else if ($key == 'console') { foreach($value as $consoleName) { $sql .= "$key = ?"; if($consoleName != end($value)) { $sql .= " OR "; } } $param = array_values($value); }
because $param simply needs to be an array with the values for the ? positional placeholders in the query. However, if you do that, you may get an error sayingCode:$param = $value;
PDO wants to bind the values in $param to the ? placeholders in the query and because they are positional, the indexes of the values in the array must be in ascending and consecutive order.Code:General error: 25 bind or column index out of range
Let's say, you pick Nintendo, Super Nintendo and Xbox. The $value array would look like this
When PDO binds those parameters, it will bind Nintendo to the first ?, because they're both at position/index 0. Then it will look for the index 1 in $param for the next ? to bind, but it can't find it because Nintendo 64 is at the index 2. The array_values() method indexes the array numerically, so $value becomesCode:[0] => Nintendo [2] => Nintendo 64 [6] => Xbox
P.S. If you have trouble with constructing your $sql, an "echo $sql;" right before the "$sth = $dbh->prepare($sql);" line (72) will dynamically show you your constructed query depending on what you entered in the form.Code:[0] => Nintendo [1] => Nintendo 64 [2] => Xbox
full page
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'); // Set PDO error handling to throw exceptions $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Define your SQL statement, myTable = table name in your DB $sql = 'SELECT rowid, title, console, genre, publisher, releaseyear, rarity, details, image FROM Games'; // If there is no POST data, create empty $param array if (empty($_POST)) { $param = array(); } // Append POST data dynamically to the query else { //Reset counter $i = 0; //Column whitelist $columns = array('title', 'console', 'genre', 'publisher', 'dateRange', 'releaseYear', 'rarity',); //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 "; } //SQL LIKE syntax for title if($key == 'title'){ $sql .= "$key LIKE ?"; $param[] = "%{$value}%"; } //Console is a multidimensional array due to multiple checkboxes else if ($key == 'console') { var_dump($value); foreach($value as $consoleName) { $sql .= "$key = ?"; if($consoleName != end($value)) { $sql .= " OR "; } } $param = array_values($value); } //Standard SQL = syntax else { $sql .= "$key = ?"; $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 place holders populated by the data from the array $sth->execute($param); // Fetch the results // $rowset = $sth->fetchAll(PDO::FETCH_ASSOC); ?> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width"> <title>NES Collection</title> <title>jQuery UI Datepicker - Default functionality</title> <link rel="stylesheet" href="//code.jquery.com/ui/1.11.2/themes/smoothness/jquery-ui.css"> <script src="//code.jquery.com/jquery-1.10.2.js"></script> <script src="//code.jquery.com/ui/1.11.2/jquery-ui.js"></script> <link rel="stylesheet" href="/resources/demos/style.css"> <script> $(function() { $( "#datepicker" ).datepicker(); }); </script> </head> <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 --> <b>Console</b><br> <input type = "checkbox" name = "game[console][0]" value = "Nintendo"> Nintendo <input type = "checkbox" name = "game[console][1]" value = "Super Nintendo"> Super Nintendo<br> <input type = "checkbox" name = "game[console][2]" value = "Nintendo 64">Nintendo 64 <input type = "checkbox" name = "game[console][3]" value = "Game Cube"> Game Cube<br> <input type = "checkbox" name = "game[console][4]" value = "Playstation"> Playstation <input type = "checkbox" name = "game[console][5]" value = "Playstation2"> Playstation2<br> <input type = "checkbox" name = "game[console][6]" value = "Xbox"> Xbox<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> Release Year: <input type="text" id="datepicker" name ="game[releaseYear]"> - <input type="text" id="datepicker" name ="game[releaseYear]> <!-- 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> <!-- Need to add in description field, currently breaks search --> <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>
-
2014-11-17 09:19 #155Salvage Bans
- Join Date
- Jun 2011
- Posts
- 927
- BG Level
- 5
- FFXI Server
- Sylph
Thanks aevis! Almost done with this thing! Tried your code and it works (although forsome reason its displaying the array of selected checkboxes at the top, but still having a few problems. 1) When i tried doing the same thing it throws errors (below) 2) Now it wont let me search with multiple conditions (IE: title = final, console = playstation1 or playstation 2). At first I thought that it might because i was doing else if statements... but with the way the loop is laid out, it shouldent matter. So still trying to investigate that.
Still have a few other things to do also but I think i can do all those by myself, except maybe the date range thing, but still working on it.
Spoiler: show
Example of string showing at top:
Example of error i'm getting when trying to do it with the other check boxes
http://bucket.bluegartr.com/53264f4b...d2065b6479.png
List of goals this week:
1) Check box that selects all options in the catagory [COMPLETE]*
2) Be able to search between start and end publisher date
3) Make check boxes look cleaner (styling) [COMPLETE]
4) Move the search to homepage (in left nav) [COMPLETE]
5) Search by title parts (IE: Super Bros) should search by titles like "Super" and "bros"
6) Finish searching by multiple parts
Current Code: (updated as progress made)
Spoiler: show
* Would like to condense my scripts into one that handles all 4 check boxes, but made it work the long way for now.
-
2014-11-19 07:09 #156Salvage Bans
- Join Date
- Jun 2011
- Posts
- 927
- BG Level
- 5
- FFXI Server
- Sylph
Updates:
1) Fixed the string showing at top (I was looking for echos and completly overlooked the var_dump)
2) Got the rest of the check boxes to search (had a space between the array name (had game[genre] [0] instead of game[genre][0]) So use to white space not mattering.
Decided to try and make the release date a 2 object array like you did the checkboxes and then go through them will the code below, but throwing up a error. I think I can get it to work this way though... maybe
Code:else if ($key == 'releaseYear') { $sql .= "$key BETWEEN ?"; $a = 0; foreach($value as $releaseName) { if($releaseName != end ($value)) { if($a == 0) { $sql .= " AND "; $a++; } else { $sql .= " ? "; } } } $param = array_values($value); }
-
2014-11-19 07:36 #157wotg torrent kitty :3
- Join Date
- Jun 2007
- Posts
- 1,643
- BG Level
- 6
Nice! You can further reduce your script if you remove all the elseif for the similar multidimensional arrays (genre, publisher, rarity) and change the first else if (console) from
toCode:else if ($key == 'console')
Code:else if(in_array($key, array('console','genre','publisher','rarity')))
-
2014-11-19 12:09 #158Salvage Bans
- Join Date
- Jun 2011
- Posts
- 927
- BG Level
- 5
- FFXI Server
- Sylph
awesome thank you. Finally got the date working! so happy. Wasnt able to figure out a way to do between but got it working with > and <. Right now I only have a few issues left to fix Originally Posted by Aevis
1) I cant do combinations of searches any more. When I run the query the last array i run is replaceing all the arrays. So for instance if i search nintendo, super nintendo, with a release year between 1980 and 1990 My query output looks like this:
UPDATE: Looks like it is something to do with $param. If using title or the else statement it works fine. I belive its because its not storing in an array (both of which use $param[] = $value instead of just $param = $value)Code:array(2) { [0]=> string(4) "1980" [1]=> string(4) "1990" } SELECT rowid, title, console, genre, publisher, releaseyear, rarity, details, image FROM Games WHERE console = ? OR console = ? AND releaseYear > ? AND releaseYear < ?
so its trying to say that where console = 1980 or console = 1990. Not really sure how to fix this
2) it is forcing me to use the release year fields now for some reason. If I don't fill in the release year blanks i get the following output.
3) I need to be able to search by pieces of the title. For instance if I search Super Bros it should actually search for title like "super" and title like "bros" instead of title like "super bros", I'm just not really sure how to break the string into diffrent parts.Code:Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1 near "releaseYear": syntax error' in E:\XAMPP\htdocs\NesList-CSS\Slider\database.php:87 Stack trace: #0 E:\XAMPP\htdocs\NesList-CSS\Slider\database.php(87): PDO->prepare('SELECT rowid, t...') #1 {main} thrown in E:\XAMPP\htdocs\NesList-CSS\Slider\database.php on line 87
Current code:
4) for simplicity I would like to be able to combine the swicthes for my checkboxes all buttons into 1 jquery statment but not on the top of my list atm.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'); // Set PDO error handling to throw exceptions $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Define your SQL statement, myTable = table name in your DB $sql = 'SELECT rowid, title, console, genre, publisher, releaseyear, rarity, details, image FROM Games'; // If there is no POST data, create empty $param array if (empty($_POST)) { $param = array(); } // Append POST data dynamically to the query else { //Reset counter $i = 0; //Column whitelist $columns = array('title', 'console', 'genre', 'publisher', 'dateRange', 'releaseYear', 'rarity',); //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 "; } //SQL LIKE syntax for title if($key == 'title'){ $sql .= "$key LIKE ?"; $param[] = "%{$value}%"; } //Console is a multidimensional array due to multiple checkboxes else if(in_array($key, array('console','genre','publisher','rarity'))) { foreach($value as $placeholderName) { $sql .= "$key = ?"; if($placeholderName != end($value)) { $sql .= " OR "; } } $param = array_values($value); } //handles the release year statments else if ($key == 'releaseYear') { $a = 0; foreach($value as $releaseName) { if($a == 0) { $sql .= "$key > ?"; $a++; } else { $sql .= "$key < ?"; } if($releaseName != end ($value)) { $sql .= " AND "; } } $param = array_values($value); } //Standard SQL = syntax else { $sql .= "$key = ?"; $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 place holders populated by the data from the array $sth->execute($param); // Fetch the results // $rowset = $sth->fetchAll(PDO::FETCH_ASSOC); var_dump($param); echo $sql; ?> <head> <title>NES Collection</title> <link rel="stylesheet" href="//code.jquery.com/ui/1.11.2/themes/smoothness/jquery-ui.css"> <script src="//code.jquery.com/jquery-1.10.2.js"></script> <script src="//code.jquery.com/ui/1.11.2/jquery-ui.js"></script> <link rel="stylesheet" href="/resources/demos/style.css"> <link id="pagestyle" href="style.css" rel="stylesheet" type="text/css"> <script type="text/javascript"> $(function() { $("#startdate").datepicker({ dateFormat: "yy" }).val() $("#enddate").datepicker({ dateFormat: "yy" }).val() }); </script> <body> <div id="header"> <h1>Retro Game Collection</h1><br><br><br> <a href = "database.php">Home</a> </div> <div id="leftNav"> <form action = "database.php" method = "post"> Search By: <br><br> <b> Title: </b> <br> <input type="Text" name="game[title]"> <br> ______________________<br> <script> $(document).ready(function() { $('#selecctall-1').click(function(event) { //on click if(this.checked) { // check select status $('.checkbox1').each(function() { //loop through each checkbox this.checked = true; //select all checkboxes with class "checkbox1" }); }else{ $('.checkbox1').each(function() { //loop through each checkbox this.checked = false; //deselect all checkboxes with class "checkbox1" }); } }); }); </script> <script> $(document).ready(function() { $('#selecctall-2').click(function(event) { //on click if(this.checked) { // check select status $('.checkbox2').each(function() { //loop through each checkbox this.checked = true; //select all checkboxes with class "checkbox1" }); }else{ $('.checkbox2').each(function() { //loop through each checkbox this.checked = false; //deselect all checkboxes with class "checkbox1" }); } }); }); </script> <script> $(document).ready(function() { $('#selecctall-3').click(function(event) { //on click if(this.checked) { // check select status $('.checkbox3').each(function() { //loop through each checkbox this.checked = true; //select all checkboxes with class "checkbox1" }); }else{ $('.checkbox3').each(function() { //loop through each checkbox this.checked = false; //deselect all checkboxes with class "checkbox1" }); } }); }); </script> <script> $(document).ready(function() { $('#selecctall-4').click(function(event) { //on click if(this.checked) { // check select status $('.checkbox4').each(function() { //loop through each checkbox this.checked = true; //select all checkboxes with class "checkbox1" }); }else{ $('.checkbox4').each(function() { //loop through each checkbox this.checked = false; //deselect all checkboxes with class "checkbox1" }); } }); }); </script> <b>Console</b><br> <input type="checkbox" id="selecctall-1"/> All <br> <input class="checkbox1" type = "checkbox" name = "game[console][0]" value = "Nintendo"> Nintendo<br> <input class="checkbox1" type = "checkbox" name = "game[console][1]" value = "Super Nintendo"> Super Nintendo<br> <input class="checkbox1" type = "checkbox" name = "game[console][2]" value = "Nintendo 64"> Nintendo 64<br> <input class="checkbox1" type = "checkbox" name = "game[console][3]" value = "Game Cube"> Game Cube<br> <input class="checkbox1" type = "checkbox" name = "game[console][4]" value = "Playstation"> Playstation<br> <input class="checkbox1" type = "checkbox" name = "game[console][5]" value = "Playstation2"> Playstation2<br> <input class="checkbox1" type = "checkbox" name = "game[console][6]" value = "Xbox"> Xbox<br> ______________________<br> <b>Genre:</b> <br> <input type="checkbox" id="selecctall-2"/> All <br> <input class="checkbox2" type = "checkbox" name = "game[genre][0]" value = "Action"> Action<br> <input class="checkbox2" type = "checkbox" name = "game[genre][1]" value = "Beatup"> Beat'em Up<br> <input class="checkbox2" type = "checkbox" name = "game[genre][2]" value = "Platform"> Platformer<br> <input class="checkbox2" type = "checkbox" name = "game[genre][3]" value = "Shooter"> Shooter<br> <input class="checkbox2" type = "checkbox" name = "game[genre][4]" value = "Adventure"> Adventure<br> <input class="checkbox2" type = "checkbox" name = "game[genre][5]" value = "rpg"> Role-Playing<br> <input class="checkbox2" type = "checkbox" name = "game[genre][6]" value = "Sim"> Simulation<br> <input class="checkbox2" type = "checkbox" name = "game[genre][7]" value = "Strategy"> Strategy<br> <input class="checkbox2" type = "checkbox" name = "game[genre][8]" value = "Sports"> Sports <br> ______________________<br> <b>Publisher: </b> <br> <input type="checkbox" id="selecctall-3"/> All <br> <input class="checkbox3" type = "checkbox" name = "game[publisher][0]" value = "Aklaim"> Aklaim<br> <input class="checkbox3" type = "checkbox" name = "game[publisher][1]" value = "Capcom"> Capcom<br> <input class="checkbox3" type = "checkbox" name = "game[publisher][2]" value = "EA"> EA<br> <input class="checkbox3" type = "checkbox" name = "game[publisher][3]" value = "KOEI"> KOEI<br> <input class="checkbox3" type = "checkbox" name = "game[publisher][4]" value = "Konami"> Konami<br> <input class="checkbox3" type = "checkbox" name = "game[publisher][5]" value = "Nintendo"> Nintendo<br> <input class="checkbox3" type = "checkbox" name = "game[publisher][6]" value = "Rare"> Rare<br> <input class="checkbox3" type = "checkbox" name = "game[publisher][7]" value = "SNK"> SNK<br> <input class="checkbox3" type = "checkbox" name = "game[publisher][8]" value = "SunSoft"> SunSoft<br> <input class="checkbox3" type = "checkbox" name = "game[publisher][9]" value = "Square Enix"> Square Enix<br> <input class="checkbox3" type = "checkbox" name = "game[publisher][10]" value = "Tecmo"> Tecmo<br> <input class="checkbox3" type = "checkbox" name = "game[publisher][11]" value = "TradeWest"> TradeWest<br> <input class="checkbox3" type = "checkbox" name = "game[publisher][12]" value = "Broderbund"> Broderbund<br> ______________________<br> <b>Release Year: </b> <br>Between</br> <input type="text" id="startdate" name="game[releaseYear][0]" size="20"/> and<br> <input type="text" id="enddate" name="game[releaseYear][1]" size="20"/> <br> ______________________<br> <b>Rarity: </b><br> <input type="checkbox" id="selecctall-4"/> All <br> <input class="checkbox4" type = "checkbox" name = "game[rarity][0]" value = "Common"> Common<br> <input class="checkbox4" type = "checkbox" name = "game[rarity][1]" value = "Uncommon"> Uncommon<br> <input class="checkbox4" type = "checkbox" name = "game[rarity][2]" value = "Rare"> Rare<br> <input class="checkbox4" type = "checkbox" name = "game[rarity][3]" value = "veryRare"> Very Rare<br> <input class="checkbox4" type = "checkbox" name = "game[rarity][4]" value = "SuperRare"> Super Rare<br> <input class="checkbox4" type = "checkbox" name = "game[rarity][5]" value = "Ultrarare"> Ultra Rare<br> ______________________<br><br><br> <input type = "submit" value = "Submit" name = "searchSubmit" style="width: 100px; height: 20px;" /> </form> </div> <div id="body"> <h1>NES Titles</h1> <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>
Thanks for all the help! and as always i'll post updates on my progress as I make any.
-
2014-11-20 03:12 #159wotg torrent kitty :3
- Join Date
- Jun 2007
- Posts
- 1,643
- BG Level
- 6
My bad, the $param = array_values($value) overwrites the entire $param array. To fix, use array_merge.
There's no need to do a foreach loop over the releaseYear array, because you'll always only have the two variables for start and end to match the query syntax.Code:else if(in_array($key, array('console','genre','publisher','rarity'))) { foreach($value as $placeholderName) { $sql .= "$key = ?"; if($placeholderName != end($value)) { $sql .= " OR "; } } $param = array_merge($param, array_values($value)); }
There's a new problem surfacing tho. Input type text fields will always submit. Even if you don't write anything into them, they will submit an empty string. So $_POST['game']['releaseYear'][0] or $_POST['game']['releaseYear'][1] will always exist and thus the !empty($value) check at the top will always return true, because it only checks $_POST['game']. Need to modify it toCode:else if ($key == 'releaseYear'){ $sql .= "$key BETWEEN ? AND ?"; $param = array_merge($param, $value); }
Code:if(!empty($value) AND ($key == 'releaseYear' && !in_array("",$value))){
-
2014-11-20 13:59 #160Salvage Bans
- Join Date
- Jun 2011
- Posts
- 927
- BG Level
- 5
- FFXI Server
- Sylph
i figured it was something with the array being overridden... but it was NOT happy about those changes lol! Get these errors when just trying to run it: Originally Posted by Aevis
Gonna look at it when i get off work tonight and try to iron it out. Thanks for the start thoughCode:Notice: Undefined variable: param in E:\XAMPP\htdocs\NesList-CSS\Slider\database.php on line 53 Warning: array_merge(): Argument #1 is not an array in E:\XAMPP\htdocs\NesList-CSS\Slider\database.php on line 53 Notice: Array to string conversion in E:\XAMPP\htdocs\NesList-CSS\Slider\database.php on line 66 Notice: Array to string conversion in E:\XAMPP\htdocs\NesList-CSS\Slider\database.php on line 66 Notice: Array to string conversion in E:\XAMPP\htdocs\NesList-CSS\Slider\database.php on line 66 Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1 near "console": syntax error' in E:\XAMPP\htdocs\NesList-CSS\Slider\database.php:77 Stack trace: #0 E:\XAMPP\htdocs\NesList-CSS\Slider\database.php(77): PDO->prepare('SELECT rowid, t...') #1 {main} thrown in E:\XAMPP\htdocs\NesList-CSS\Slider\database.php on line 77
Reply With Quote
Similar Threads
-
Simple HTML issue with my website, need help.
By Avanarius in forum TechReplies: 3Last Post: 2011-04-29, 05:09 -
HTML / VBS / Java Script Gurus -- Help Needed!
By Obiron in forum TechReplies: 14Last Post: 2009-09-11, 13:12