HTML Website help

**Aylee** · 2014-10-18 09:23

Originally Posted by 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:

Code:

     <?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:

Code:

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:

Code:

<?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:

Code:

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

Ok tried your first bit of code and this was the result lol

The rest of the code i think is for the search function? kinda confused me lol but trying to work my way though it and see what i can accomplish.

Here was my idea for making what your trying to do work. I was going to make it so that every title is the same clickable link. When you click the link the title is stored, and the user is redirected to a new page (lets say gameDetails.php), and the new page is passed on the titles name. The page then basically just loads the site template and instead of loading the entire database it loads the info based on the title.

New pages code would look 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");
				 
		// Define your SQL statement, myTable = table name in your DB //
		$sql = "SELECT * FROM Games WHERE TITLE = 'Blaster Master'";
			 
		// 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>

	</body>
	
</html>

but instead of just displaying the result as a database it would display the information in a normal page layout style. I think I can make it work work super easily with the style im trying to do it. I already have all the titles as hyper links leading to gameDetails.php, I just need to have a way to pass the clicked games value to gameDetails.php

Found this but about to have to leave for work. Is this setting me on the right path?
http://stackoverflow.com/questions/8...e-to-next-page

**Corrderio** · 2014-10-18 17:17

Holy hell did I ever botch that... I better look up alternate syntaxes a bit more in the near future.

Anyway for the questions. The 2nd is just an example of what the URL would look like with the parameters we pass it, the 3rd is the code to get the parameters from the URL, and last block is the SQL query. Assuming you go with my idea. This is an area where Aevis would be of more help.

Anyway, I'll look into the screw up later tonight.

**Aylee** · 2014-10-18 19:32

I would like to try and get my way working. I have a test version of it working perfectly. Using the following:
On database.php Use this to send the title to the new page

Code:

<?php
if(!empty($_FILES)){
session_start();

$_SESSION['title'] = $gameTitle;
}
?>

<form method="get" action="test2.php">
    <input type="text" name="title" value="">
    <input type="submit">
</form>

On the gameDetails page use this to get the title from other page:

Code:

<?php

session_start();

$gameTitle = $_GET['title'];

echo "The game title is: ".$gameTitle.".";

?>

Now if someone could just help me figure out how to pass the title that was clicked on to the $session variable :/

**Aevis** · 2014-10-19 04:19

I'm on a bachelor weekend in Prague, so I'll join this on Monday again

**Aylee** · 2014-10-19 07:36

Originally Posted by Aevis

I'm on a bachelor weekend in Prague, so I'll join this on Monday again

Sweet! Enjoy your self

**Corrderio** · 2014-10-20 09:30

Bleh, sorry I didn't get a chance to help out last night. Felt like crap throughout the good half of the night.

**Aylee** · 2014-10-20 14:45

No problem, right now my biggest hang up is storing the title that was clicked. Once I figure out how to get the title that was clicked I'll have most of it done except the search

**Corrderio** · 2014-10-20 14:52

Ever fix what I botched?

**Aylee** · 2014-10-20 17:47

Originally Posted by Corrderio

Ever fix what I botched?

Not yet, if I can figure out a way to get this done I won't have to do it the way you di it. Your way is probably better and more efficient, but I understand my way more, and will be able to do it quicker (to meet the Wednesday deadline, and won't need to rely on you guys as much)

**Corrderio** · 2014-10-20 18:56

Yeah, my way was mostly for if you had an autoincrement so you could search by that by just changing the number in your browser window

**Aylee** · 2014-10-20 19:14

Yeah if I can just figure out how to capture the clicked title I'll have it made I think haven't had much time to work on it though. Tomorrow gonna be grinding it out all day after class

**Aevis** · 2014-10-21 04:23

Back and somewhat alive again.

I read through the last two pages and from what I gathered, you're only looking for a solution to the click-a-link problem by now. Corrderio was right, that for this an autoincremental column is the way to go. Otherwise, you'd need to create artifical indexes on the table by a combination of multiple columns. The good news is, SQLite already creates such a column for you, called ROWID (https://www.sqlite.org/autoinc.html).

A note on the different super global variable types: SESSION is used to share consistent data, don't use it for user requests. POST is used for user submitted data to store, e.g. registration form. GET is used for user request data, e.g. google search. The main technical difference between POST and GET is, that GET data appends to the URL, e.g. searchResults.php?rowid=2 would send a $_GET['rowid'] variable with a value of 2 to searchResults.php. So for your page, you have to add a hyperlink to each title column and append the related rowid. I took your code from the last page and cut it down to reflect only this case:

database.php

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 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="body">
            <h1>NES Titles</h1>                        
            <div id="LowerBody">
                <table border ="1" width: "100%">
                    <thead>
                        <tr>
                            <th>Title</th>
                            <th>Console</th> 
                            <th>Genre</th>
                            <th>Publisher</th>
                            <th>Release Year</th>
                            <th>Rarity</th>
                        </tr>
                    </thead>
                    <?php foreach($rowset as $row): ?>
                        <tr>
                            <td>
                                <a href="searchResults.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>
                        </tr>
                    <?php endforeach; ?>
                </table>            
            </div>        
        </div>

    </body>
    
</html>

Most should be self-explanatory. There's one caveat with the rowid column that SQLite automatically generates: If you use a placeholder in your SELECT query (SELECT * FROM Games), it will not get the rowid and you'll receive an error that $row doesn't have a 'rowid' index. You have to call it explicit (SELECT rowid FROM Games). You could do SELECT rowid, * FROM Games to not list every single column, but I prefer to do it anyway. That way you know which data you get without looking into the DB to make sure.

With the rowid appended to the URL, you can call it in your searchResults.php and use it to search the DB again to display additional data:

searchResults.php

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 title FROM Games WHERE rowid = :rowid";
             
        //Prepare the query
        $sth = $dbh->prepare($sql);
        
        //Create array with the named place holders as keys and form data as values
        $data = array(
            ':rowid' => $_GET["rowid"],
        );

        //Execute the query with the place holders populated by the data from the array
        $sth->execute($data);
        
        // Fetch the results // 
        $row = $sth->fetch();
                        
    ?>
            
    <head>
    </head>
   
    <body>

        <div>
            You clicked the following title: <?php echo $row['title']; ?>
        </div>
    
    </body>
    
</html>

**Aylee** · 2014-10-21 07:47

Originally Posted by Aevis

Back and somewhat alive again.

I read through the last two pages and from what I gathered, you're only looking for a solution to the click-a-link problem by now. Corrderio was right, that for this an autoincremental column is the way to go. Otherwise, you'd need to create artifical indexes on the table by a combination of multiple columns. The good news is, SQLite already creates such a column for you, called ROWID (https://www.sqlite.org/autoinc.html).

A note on the different super global variable types: SESSION is used to share consistent data, don't use it for user requests. POST is used for user submitted data to store, e.g. registration form. GET is used for user request data, e.g. google search. The main technical difference between POST and GET is, that GET data appends to the URL, e.g. searchResults.php?rowid=2 would send a $_GET['rowid'] variable with a value of 2 to searchResults.php. So for your page, you have to add a hyperlink to each title column and append the related rowid. I took your code from the last page and cut it down to reflect only this case:

database.php

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 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="body">
            <h1>NES Titles</h1>                        
            <div id="LowerBody">
                <table border ="1" width: "100%">
                    <thead>
                        <tr>
                            <th>Title</th>
                            <th>Console</th> 
                            <th>Genre</th>
                            <th>Publisher</th>
                            <th>Release Year</th>
                            <th>Rarity</th>
                        </tr>
                    </thead>
                    <?php foreach($rowset as $row): ?>
                        <tr>
                            <td>
                                <a href="searchResults.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>
                        </tr>
                    <?php endforeach; ?>
                </table>            
            </div>        
        </div>

    </body>
    
</html>

Most should be self-explanatory. There's one caveat with the rowid column that SQLite automatically generates: If you use a placeholder in your SELECT query (SELECT * FROM Games), it will not get the rowid and you'll receive an error that $row doesn't have a 'rowid' index. You have to call it explicit (SELECT rowid FROM Games). You could do SELECT rowid, * FROM Games to not list every single column, but I prefer to do it anyway. That way you know which data you get without looking into the DB to make sure.

With the rowid appended to the URL, you can call it in your searchResults.php and use it to search the DB again to display additional data:

searchResults.php

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 title FROM Games WHERE rowid = :rowid";
             
        //Prepare the query
        $sth = $dbh->prepare($sql);
        
        //Create array with the named place holders as keys and form data as values
        $data = array(
            ':rowid' => $_GET["rowid"],
        );

        //Execute the query with the place holders populated by the data from the array
        $sth->execute($data);
        
        // Fetch the results // 
        $row = $sth->fetch();
                        
    ?>
            
    <head>
    </head>
   
    <body>

        <div>
            You clicked the following title: <?php echo $row['title']; ?>
        </div>
    
    </body>
    
</html>

So basically its doing the same thing I was trying to do, but it just gets the row id and then finds echos the title in that row? Worked perfectly for that part I was working on. I still have 2 more things I need to do (i'll work on and post if i get stuck this afternoon after class). One thing I am confused about is on the new page that loads with the results of the clicked title. Why cant I say for instance <?php echo $row['console']; ?> in the SQL statment it is selecting * from games so it should bring in all the information including console, genre, rarity, ext right?

1) Get an actual search working (type a title into the text box and search for titles that match it, display results, and have the clickable link to view the results)

2) I have the image uploading work, but it just saves to a file. I need to make it so that when the image is saved i can save it named as title_console.jpg (figured if i do it this way I dont have to worry about saving it to the database, i can just call it based on title/console) unless saving to the database is super simple which I tink it would just be saving the file name to the database? lol

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

Ok Here is Where I'm at... And I THINK i'm almost done with this weeks part excluding 1-2 little things, Unless You guys see something I'm missing.

1)I have database.php I deleted the old database and created another one with 2 new fields (image path, and details). Displays fine, picture uploads but the problem i am having with adding a new addition to database is with the picture. It is not storing the picture name into the database for some reason.

Code:

<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 = "SELECT rowid, title, console, genre, publisher, releaseyear, rarity, details, image 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><br><br><br>
			<a href = "database.php">Home</a>
			<a href = "Search.php">Search</a>
		</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>
						<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>
			
			<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>
					Details:<br>
					<textarea name = "details" rows ="4" cols="50">
					</textarea><br>
					
					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);        
						}
					?>
					
				</form>
			</div>
		</div>

	</body>
	
</html>

2) When you click on any of the titles it does go to the next page gameDetails.php, but its not passing all the values for some reason (i might just be calling them wrong not sure). It gets the title (from both database.php and search.php) but its wont let me pull the rest of the information from the row.

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 title FROM Games WHERE rowid = :rowid";
             
        //Prepare the query
        $sth = $dbh->prepare($sql);
        
        //Create array with the named place holders as keys and form data as values
        $data = array(
            ':rowid' => $_GET["rowid"],
        );

        //Execute the query with the place holders populated by the data from the array
        $sth->execute($data);
        
        // Fetch the results // 
        $row = $sth->fetch();
                        
    ?>
            
	<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="detailsBody">
			<h1><?php echo $row['title']; ?></h1>
			<img src="images/EarthBound_Super Nintendo.jpg" > <br> <!-- This needs to be Coded to work with title selection -->
			Console: <?php echo $row['console']; ?> <br> 
			Genre: <?php echo $row['Genre']; ?> <br>
			Publisher: <?php echo $row['Publisher']; ?> <br>
			Release Year: <?php echo $row['Release Year']; ?> <br>
			Rarity: <?php echo $row['Rarity']; ?> <br>
			
			
		</div>
	</body>
	
</html>

3) I have a link to search.php in order to search via the database. My goal is for when the page is loaded it shows the entire database, but when you type a word into the search field and hit search it refreshes the database with only titles matching the entered word. I should just be able to change the sql statement that shows the database but kinda confused atm but working on this as we speak.

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";
             
        // 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><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">
				Search By Title: <input type="Text" name="title"><br>
				<input type = "submit" value = "Submit" name = "searchSubmit" style="width: 100px; height: 50px;" />
			</form>
			
								<?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);        
						}
					?>
		</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>

Reiteration of current issues:
1) Add game to database not adding image name
2) gameDetails.php not pulling all values from the database (ie console/genre)
3) search doesn't search lol

**Aevis** · 2014-10-21 15:42

I'll have a look at 1 and 3 tomorrow, but for 2 check your SELECT query. You're only querying the title.

**Aylee** · 2014-10-21 17:36

Originally Posted by Aevis

I'll have a look at 1 and 3 tomorrow, but for 2 check your SELECT query. You're only querying the title.

OH... DUH lmao fixed #2. lol still working on num 1 mainly, trying to have it finished by morning

**Aevis** · 2014-10-22 04:03

#3. It doesn't search because you aren't fetching the results from the query with the submitted title variable, you're only executing it. The foreach further down is populated by the data from the first query on top, which querys all entries from the Games table. You can combine the general and specific query on top, because you don't need to post PHP code at any specific place except if you want to output it into HTML at that exact point, e.g. the foreach loop:

search.php

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['title'])) 
        { 
            $sql .= ' WHERE title LIKE :title'; 
            $param = array(':title' => "%{$_POST['title']}%");
        }
        else { $param = array(); }
        
        // Apply statement 
        $sth = $dbh->prepare($sql);
        
        // Execute the query with the place holders populated with the data from the array 
        $sth->execute($param);

        // Fetch the results 
        $rowset = $sth->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><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">
                Search By Title: <input type="Text" name="title"><br>
                <input type = "submit" value = "Submit" name = "searchSubmit" style="width: 100px; height: 50px;" />
            </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>

Notes about odd/new looking syntax:

".=" is a string operator which appends the argument on the right side to the argument on the left. So if $_POST['title'] has a value, the regular SELECT query is appended with the condition. The query in $sql becomes "SELECT rowid, title, console, genre, publisher, releaseyear, rarity, details, image FROM Games WHERE title LIKE :title".

As Corrderio exlpained it earlier, to make a SELECT query with a LIKE condition work, you have to use the % wildcard. Otherwise it would look strictly for your term. With PDO and placed nameholders, the syntax looks a bit wierd. You could use either

Code:

array('title' => '%'.$_POST['title'].'%')

Code:

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

IMO the second version is easier to read and curly braces inside a double quote lets you use the same syntax for the variable as you would use outside of the string. The only important thing is, that you add the wildcard in the array which you use in the execute and not directly try to insert them in the query.

Code:

SELECT * FROM Games WHERE title LIKE %:title%

or similar will not work. Reason beeing all the escaping magic that PDO does for you, when it binds the array to the placeholder in the query.

**Aylee** · 2014-10-22 15:40

Aevis/corredrio do either of you play Smite? I got my loot crate in today and have 2 codes for the jack the reaper skin. Figured I would give you two first shot at them if you needed them.

**Aevis** · 2014-10-22 15:58

Thanks, but not needed as I don't play it or intend to. The only MOBA I occasionally play is LoL.

**Corrderio** · 2014-10-22 16:11

What Aevis said, but not even LoL. MOBA communities are cringe-worthy from my experience. That and I'm not into competitive games other than Team Fortress 2 since I'm a casual.

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