CODEWITHSUNDEEP
phpmysqlget
mythoslife
mythoslife

Reputation: 213

Variable URL PHP and MySQL

Just trying to pass a variable on URL so that when echoed I can click on it and open it's own content based on the database record. Right now this one shows all the records from database but what I was trying to do was pass a URL so each blog IDs will have it's own URL and when clicked on it will open the individual entries rather than all the entries.

Edited Now I'm able to show rows of entries with IDs where 'IDs' has URL variable at the end. Do I need to create another query to echo the individual entry on my mini blog?

<?

$db = // connection to db and authentication to connecting to db;

#$postID = $_GET['postID']; // I'm thinking to use a $_GET global variable to work with URL variable
$command = "select * from $table_name"; // I'm thinking to add the Id here or something or create another query to echo the linked URL 'viewblog.php?postID=$data->blogID'
$result = $db->query($command);

while ($data = $result->fetch_object()) {
echo "<TR><TD><a href='viewblog.php?postID=$data->blogID'>".$data->blogID."</a></TD>";
echo "<TD>".$data->author."</TD>";
echo "<TD>".$data->date."</TD>";
echo "<TD>".$data->entry."</TD></TR>\n";
}
$result->free();
$db->close;

Upvotes: 1

Views: 1349

Answers (4)

mythoslife
mythoslife

Reputation: 213

<?php
//$db connect to database

// Entry form sanitation of $_POST

// Insert PHP file to MySQL

// View all blog posts

$postID = $_GET['postID']; // I guess I should sanitize this as well
if (!empty($postID)) {
  $command = "select * from $table_name where blogID = $postID";
  $result = $db->query($command);
  while ($data = $result->fetch_object()) {
    $postID = $data->blogID;
    echo "<TR><TD>".$postID."</TD>";
    echo "<TD>".$data->author."</TD>";
    echo "<TD>".$data->date."</TD>";
    echo "<TD>".$data->entry."</TD></TR>\n";
  }
  $result->free();  
}
else {
$command = "select * from $table_name"; 
$result = $db->query($command);
while ($data = $result->fetch_object()) {
   $postID = $data->blogID;
   echo "<TR><TD><a href='viewblog.php?postID=$postID'>".$postID."</a></TD>";
  echo "<TD>".$data->author."</TD>";
  echo "<TD>".$data->date."</TD>";
  echo "<TD>".$data->entry."</TD></TR>\n";
}
$result->free();
}


$db->close;

?>

Upvotes: 0

Ahmed Sagarwala
Ahmed Sagarwala

Reputation: 410

You are selecting all entries from your table. Use the following:

$db = // connection to db and authentication to connecting to db;

$postID = $_GET['postID']; // ??
$db->real_escape_string(trim($postID));
$command = "select * from $table_name WHERE `postID`=$postID";
$result = $db->query($command);
// Ensure results before outputting
if ($result->num_rows) while($data = $result->fetch_assoc()){
  $data['blogID'] = $postID;
  echo "<TR><TD><a href='viewblog.php?postID='>".$data['blogID']."</a> </TD>"; //??
  echo "<TD>".$data['author']."</TD>";
  echo "<TD>".$data['date']."<BR></TD>";
  echo "<TD>".$data['entry']."</TD></TR>\n";
} else echo "No entry found!";
$result->free();
$db->close;

Upvotes: 0

Braza
Braza

Reputation: 425

Why this script is giving all entries?

Because the final query that is being sent to the database is something like 

select * from TABLE_NAME

which will return all entries since your are using the asterix * after SELECT

What you are asking for can be obtained if the executed final query contains the "blogID" before retrieving the results and start fetching them.

http://www.w3schools.com/sql/sql_where.asp

You should also use the fetched or post ID in the echoed result (so that when clicked, each blog has its own id in the link).

It could be something like this

$postID = $_GET['postID']; 

//Add filtering by id to select statement
$command = "select * from '$table_name' obj WHERE obj.blogID = '$postID'";

$result = $db->query($command);
while($data = $result->fetch_assoc()){
$data['blogID'] = $postID; 

//Add ID to echoed link 
echo "<TR><TD><a href='viewblog.php?postID='".$data['blogID']."> Some Blog  (ID: ".$data['blogID'].") </a> </TD>"; 
echo "<TD>".$data['author']."</TD>";
echo "<TD>".$data['date']."</TD>";
echo "<TD>".$data['entry']."</TD></TR>\n";
}

WATCH OUT for security issues regarding this code. You should use a safer way to do this. I'm only explaining the results.

As for Auto Increment, it can be initiated when you first created the table. This is for when you INSERT a new row into the database. When you use Auto Increment, you don't have to give an ID manually.

http://www.w3schools.com/sql/sql_autoincrement.asp

Notice : The HTML BR ELEMENT should not be used inside TABLE structures.

Hope it helps.

Upvotes: 3

user1607528
user1607528

Reputation:

You could create some function like this for returning single post based on url

function single_blog($Post_id){
$sql = "SELECT * FROM your_table WHERE post_id = ? LIMIT 1";
$stmt = $this->db->prepare($sql);
$stmt->execute(array($Post_id);
return $stmt->fetch();
}

Upvotes: 0

Related Questions