HTML Website help

[Aylee]

Yeah, $_FILES doesn't have any index because it doesn't exist before you upload something. Check if the array is empty before executing your upload code

if(!empty($_FILES))
{
  all your upload code here
}

worked perfect thank you. Now professor wants me to be able to search the database. My plan is to do it exactly the same as the add game thing, but just switch the sql statement from adding to "select * from games where ??? = ??? That would be the easiest way correct?

[Corrderio]

Assuming your column for name is simply name, the typical syntax would be:

SELECT * FROM Games WHERE name = '$name'

The * is used to return all column values for rows that meet that criteria and $name is the variable for your search token.

However, the issue in this case is your variable would have to match what you're searching for exactly. In this case, I'd use the LIKE operator:

SELECT * FROM Games WHERE name LIKE '%$name%'

The LIKE is used to find a specific pattern. The % are wildcards that are telling it to search for whatever you send anywhere in the name column.

However, one more issue persists, case sensitivity. This can be fixed by forcing the SQL and your variable to both be uppercase. So it would look something like this:

$name = strtoupper ( string $name );
SELECT * FROM Games WHERE UPPER(name) LIKE '%$name%'

The first line is PHP code where you convert the string you made into uppercase. The second is our SQL statement with our WHERE clause checking the records in the name column when everything is uppercase. This way you can use mixed case when you search by name. For example, let's say you wanted to search for Crystalis, you could enter Crystalis, crystalis, CRYSTALIS, CrYsTaLiS, etc. and it would return the game or even if you remove a few characters at the start and/or end due to the wildcards in the SQL statement.

[The Stig]

Question, just curious really. I'm really happy that you're progressing well (infact, I forgot most of this stuff nowadays because I just find a Content Management System and use that. I'm too lazy to code manually unless it's tasked at work by the client), but in what manner or sense is this now a simple website from the professor haha

I hope you get 110% marks for this. I wonder if anyone else is putting in the effort. But you're learning to create a content system either way, and you're doing it in the way that's most optimal (post code, ask questions, and keep feeding back to us; most people take the code and just want it to work)


Regarding the search statement, pray to god he doesn't ask for a more strict/specific search. It is no fun to code. I predict he might ask for a specific criteria (i.e. game category) to be searched within, but that is just simply adding an additional WHERE value.

[Corrderio]

Regarding the search statement, pray to god he doesn't ask for a more strict/specific search. It is no fun to code. I predict he might ask for a specific criteria (i.e. game category) to be searched within, but that is just simply adding an additional WHERE value.

I never had to do anything like this before, but dear lord this would be a major pain in the ass even with a basic idea.

[Aylee]

Question, just curious really. I'm really happy that you're progressing well (infact, I forgot most of this stuff nowadays because I just find a Content Management System and use that. I'm too lazy to code manually unless it's tasked at work by the client), but in what manner or sense is this now a simple website from the professor haha

I hope you get 110% marks for this. I wonder if anyone else is putting in the effort. But you're learning to create a content system either way, and you're doing it in the way that's most optimal (post code, ask questions, and keep feeding back to us; most people take the code and just want it to work)


Regarding the search statement, pray to god he doesn't ask for a more strict/specific search. It is no fun to code. I predict he might ask for a specific criteria (i.e. game category) to be searched within, but that is just simply adding an additional WHERE value.

Yeah this is snowballing quickly into a complicated mess lol. But yeah stuff is coming along nicely. Also I really appreciate all the help! Its nice actually learning PHP since my school never teaches it.

What he is really wanting apparently is where i display the database on the bottom of the page, make the title a link and when clicked on it brings up the picture of the game, description, and all the information. And Yes I think the search is gonna end up being specific but would unless he makes me change It i'm going to just make it searchable by 1 criteria at a time lol.

[Corrderio]

So basically what you need is something where you click the name of the game it shows the full details of the game on a new page?

[Aylee]

So basically what you need is something where you click the name of the game it shows the full details of the game on a new page?

Yep, but it has to load the information onto the new page based on the database. I think with code posted I have the means to do it though.

[Corrderio]

Well before we go on, did you take my advice and make an autoincrement field as your table's primary key?

[Aylee]

Well before we go on, did you take my advice and make an autoincrement field as your table's primary key?

Honestly no. I must have overlooked where you talked about that sorry. Actually I didn't actually manually set anything as the primary key. So unless SQLite auto sets one it currently doesn't have a primary key. What would be the purpose of a autoincrement field? Not saying it's a bad idea just can't think of a use off top of my head

[Aylee]

I wont have time to work on the site until tomorrow but this is my plan and how I intend to accomplish it. Please let me know if you have a better way to do it.

1) Enable the search:

I'm going to make a left nav pane (already know how to do this), then I'm going to put in a textfield. Basically copy paste the "add game to database code" but change the sql statement from adding to a select * from Games where title = $entry. Also I plan on making the form load a new page displaying the results (with the same method that was used to display the original database)

2) Link to a detailed page:

With the results displayed from the search (and eventually the full database at the bottom of the home page) I want to turn the title of each game into a link. The link will take the user to another page and that page will load the information from the database based on the selected title. I have a rough idea how to format the new page but 100% clueless on how to turn the title into a hyperlink.

3) create the new page that pulls the selected games information from the database (including the picture)

[Corrderio]

Well if you don't want to recreate the table for the new column, that's fine. Anyway I have to head to work soon, the gist of why an autoincrement is a good idea since your name field isn't a real primary key since it could be possible to have 2 games by the samn name in your database, but on different consoles.

[Aylee]

Well if you don't want to recreate the table for the new column, that's fine. Anyway I have to head to work soon, the gist of why an autoincrement is a good idea since your name field isn't a real primary key since it could be possible to have 2 games by the samn name in your database, but on different consoles.

So just make title + console the primary key?

So I started on the search. Main issue i'm having is seperating the php statments lol. When I try to enter a title into the search i get a error saying console, rarity, ext arent defined (basically its calling the add game to database button instead. This is what I have:

		<div id="leftNav">
			<form action = "searchResults.php" method = "post">
				<h3>Search: </h3> <br>
				Title: <input type="Text" name="title"><br>
				<input type = "submit" value = "Submit" name = "search" style="width: 75px; height: 50px;" />
				
				<?php
					if (empty($_POST)) {
						//No form data found, moving on    
					}
					else {
						//Creating the string for the SQL statement with named placeholders
						$sql = "Select * FROM GAMES WHERE title = ':title'";

						//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"],
						);
						
						//Execute the query with the place holders populated by the data from the array
						$sth->execute($data);        
					}
				?>				
			</form>
		</div>

Also made a php site.

Full code for database.php:

<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 * FROM Games";
			 
		// Apply statement //
		$statement = $dbh->query($sql);

		// Fetch the results // 
		$rowset = $statement->fetchAll();
	?>
	

			
	<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>
		</div>

		<div id="leftNav">
			<form action = "searchResults.php" method = "post">
				<h3>Search: </h3> <br>
				Title: <input type="Text" name="title"><br>
				<input type = "submit" value = "Submit" name = "search" style="width: 75px; height: 50px;" />
				
				<?php
					if (empty($_POST)) {
						//No form data found, moving on    
					}
					else {
						//Creating the string for the SQL statement with named placeholders
						$sql = "Select * FROM GAMES WHERE title = ':title'";

						//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"],
						);
						
						//Execute the query with the place holders populated by the data from the array
						$sth->execute($data);        
					}
				?>				
			</form>
		</div>
		
		<div id="body">
			<h1>NES Titles</h1>
			<!-- This controlles and conatins the 3 collumn text boxes -->
			<div id="contentBox" style="margin:0px auto; width:100%">
				<div id="column1" style="float:left">
					content (scrolls overflow)
				</div>
				<div id="column2" style="float:left">
					content (scrolls overflow)
				</div>
				<div id="column3" style="float:left;">
					content (scrolls overflow)
				</div>
			</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>
					</tr>
					<tr>
						<?php foreach ( $rowset as $row ) : ?>
							<td><?=$row['title']?></td>
							<td><?=$row['console']?></td>
							<td><?=$row['genre']?></td>
							<td><?=$row['publisher']?></td>
							<td><?=$row['releaseYear']?></td>
							<td><?=$row['rarity']?></td></tr>
						<?php endforeach; ?>
				</table>			
			</div>
			
			<div id="midBody">
				<form action = "database.php" method = "post" enctype="multipart/form-data">
					Add a Game to the database:<br>
					
					<!-- Input field for the game title -->
					Title: <input type="Text" name="title"><br>
					
					<!-- Drop down box for the console -->
					Console:
					<select name = "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= "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 = "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="releaseYear"><br>
					
					<!-- Drop down box for the Rarity option -->
					Rarity:
					<select name= "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>
					
					Upload Image: <input type="file" name="uploadFile"><br>
								
					<input type = "submit" value = "Submit" name = "gameSubmit" style="width: 100px; height: 50px;" />
					
					<!-- php code to upload the image to the "image" folder -->
		
						<?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)";

							 //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"],
							);
							
							//Execute the query with the place holders populated by the data from the array
							$sth->execute($data);        
						}
					?>
					
				</form>
			</div>
		</div>

	</body>
	
</html>

Code for searchResults.php (way im displaying the search results)

<html>
<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 = "";
			 
		// Apply statement //
		$statement = $dbh->query($sql);

		// Fetch the results // 
		$rowset = $statement->fetchAll();
	?>
	

			
	<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>
		</div>

		<div id="leftNav">
			<form action = "database.php" method = "post">
				<h3>Search: </h3> <br>
				Title: <input type="Text" name="title"><br>
				<input type = "submit" value = "Submit" name = "gameSubmit" style="width: 75px; height: 50px;" />
				
				<?php
					if (empty($_POST)) {
						//No form data found, moving on    
					}
					else {
						//Creating the string for the SQL statement with named placeholders
						$sql = "Select * from Games where title = ':title'";

						//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"],
						);
						
						//Execute the query with the place holders populated by the data from the array
						$sth->execute($data);        
					}
				?>				
			</form>
		</div>
		
		<div id="body">
			<h1>NES Titles</h1>
			<!-- This controlles and conatins the 3 collumn text boxes -->
			<div id="contentBox" style="margin:0px auto; width:100%">
				<div id="column1" style="float:left">
					content (scrolls overflow)
				</div>
				<div id="column2" style="float:left">
					content (scrolls overflow)
				</div>
				<div id="column3" style="float:left;">
					content (scrolls overflow)
				</div>
			</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>
					</tr>
					<tr>
						<?php foreach ( $rowset as $row ) : ?>
							<td><?=$row['title']?></td>
							<td><?=$row['console']?></td>
							<td><?=$row['genre']?></td>
							<td><?=$row['publisher']?></td>
							<td><?=$row['releaseYear']?></td>
							<td><?=$row['rarity']?></td></tr>
						<?php endforeach; ?>
				</table>			
			</div>
	
	</body>
	
</html>

And if for some reason a full zip of what I have would help here it is:
https://drive.google.com/file/d/0B1A...ew?usp=sharing

[Corrderio]

Problem with primary keys is they need to be unique, so console wouldn't work. However, you are somewhat right. What you could do is search for both the name of the game and the console since the odds of having 2 games with the name on the same console are slim to none.

Can you post me your homepage code?

[Aylee]

Problem with primary keys is they need to be unique, so console wouldn't work. However, you are somewhat right. What you could do is search for both the name of the game and the console since the odds of having 2 games with the name on the same console are slim to none.

Can you post me your homepage code?

Homepage is database php

[Corrderio]

Whoops. Did not see that. Yay for already being checked out on Friday.

Anyway the only place we should have to alter code is in your LowerBody container.

First thing I'd do is fix your tr tag. I noticed the opening tag is outside of the loop, meaning you're only creating one opening tag, but multiple closing tags, so let's fix that:

     <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>
               </tr>
               <?php foreach ( $rowset as $row ) : ?>
                    <tr><td><?=$row['title']?></td>
                    <td><?=$row['console']?></td>
                    <td><?=$row['genre']?></td>
                    <td><?=$row['publisher']?></td>
                    <td><?=$row['releaseYear']?></td>
                    <td><?=$row['rarity']?></td></tr>
               <?php endforeach; ?>
          </table>			
     </div>

I'll post the rest later. Right now I'm going to grab lunch and look into some more on this.

[Obsidian]

What you could do is search for both the name of the game and the console since the odds of having 2 games with the name on the same console are slim to none.

Need for Speed: Most Wanted.

I haven't read what other fields are being stored in the table but name + console + release date should be unique enough if all of the aforementioned are being recorded.

[Corrderio]

Okay back. Now for the not-so-fun part. Also, this is more unfamiliar territory for me so there's a good chance Aevis will save this.

To display the data what we're going to do is make your title into a URL that will send the title and console over as parameters through the URL to a new page (gameDetails.php). The catch though is that we can't have any spaces in the URL or we'll break it. So what we'll do is create a strings for the title and console and replace the spaces with another character (in this case, +) like so:

     <?php foreach ( $rowset as $row ) : ?>
          $sTitle = str_replace(" ", "+", <?=$row['title']?>);
          $sConsole = str_replace(" ", "+", <?=$row['console']?>);

          <tr><td><a href="gameDeatils.php/?title='$sTitle'&console='$sConsole'><?=$row['title']?></a></td>
          <td><?=$row['console']?></td>
          <td><?=$row['genre']?></td>
          <td><?=$row['publisher']?></td>
          <td><?=$row['releaseYear']?></td>
          <td><?=$row['rarity']?></td></tr>
     <?php endforeach; ?>

Now, assuming I did that right (Kinda doubt it... *Lights the Aevis signal*), your URL should look something like this if we clicked Blaster Master:

gameDetails.php?title=Blaster+Master&console=Nintendo

Now that we got the the parameters, now we need to get them and replace the +s with spaces again then make our SQL statement:

<?php
          //Getting Variables
          $sTitle = htmlspecialchars($_GET["title"])
          $sConsole = htmlspecialchars($_GET["console"])

          //Replace + with spaces
          $sTitle = str_replace("+", " ", $sTitle);
          $sConsole = str_replace("+", " ", $sConsole);
?>

After that, yoru SQL query will be like so:

$sql = "SELECT * FROM Games WHERE title '$sTitle' AND console = '$sConsole'";

[Aylee]

OK thanks. I'm at work till late tonight, but I'll try to make some progress with what you said before bed.

[Corrderio]

About the auto increment? If so, you can just use that to search by which would cut some of the code I added.

[Aylee]

I don't mind doing it if it will really help that much. Just not 100% sure how to do it (assuming a I++ type thing) and just wanna get stuff working lol trying to have all 3 things working by Wednesday morning.

Also I'm at work so just skimming over post atm I'll read in depth later tonight but your trying to accomplish turning the title of each game into a link that takes the user to the page displaying the gsme information correct?