Priyanshu Kumar
Reputation: 3
SQL Query not retrieving proper results
Here's my login.php script I use to determine whether or not to let a user in.
<?php
if(isset($_POST['submitted']))
{
$errors= array();
$username = ($_POST['username']);
$pass = ($_POST['pass']);
$shapass = sha1($pass);
$_POST['username'] = filter_var($_POST['username'], FILTER_SANITIZE_STRING);
if ($_POST['username'] == "") {
$errors[] = "Please enter your username.";
}
if ($_POST['pass'] == "") {
$errors[] = "Please enter your password.";
}
if(is_array($errors))
{
echo '<font color="red"><div align="center" class="error"><span></span><ul>';
while (list($key,$value) = each($errors))
{
echo '<li>'.$value.'</li><br />';
}echo'</ul></div></font>';
}
if(empty($errors))
{
$user_name = "root";
$password = "";
$database = "rsswebapp";
$server = "127.0.0.1";
$db_handle = mysql_connect($server, $user_name, $password);
$db_found = mysql_select_db($database, $db_handle);
if ($db_found)
{
$match = "Select ROWID,EMAIL,PWD from `user` ".
" where (USERNAME='$username' OR EMAIL='$username') and PWD='$shapass' ";
$qry = mysql_query($match);
/*
$uid = isset($_POST['username']) ? $_POST['username'] : $_SESSION['username'];
$pwd = isset($_POST['pass']) ? $_POST['pass'] : $_SESSION['pass'];
*/
$num_rows = mysql_num_rows($qry);
if ($num_rows <= 0) {
//unset($_SESSION['uid']);
//unset($_SESSION['pwd']);
header('location:index.php?msg=' . urlencode(base64_encode("Sorry, there is no username $username with the specified password.Try again!")));
}
else
{
session_start();
$_SESSION['ROWID'] = $sid ;
header("location:dashboard.php");
}
mysql_close($db_handle);
}
else {
print "Database NOT Found ";
mysql_close($db_handle);
}
}
}
?>
If login is successful, I redirect user to dashboard.php
<body>
<div class="container">
<div id="sidebar">
<ul>
<li><a href="dashboard.php?p=categories"><center>Categories</center></a></li>
<li><a href="dashboard.php?p=myfeeds"><center>My Feeds</center> </a></li>
<li><a href="dashboard.php?p=managefeeds"><center>Manage Feeds</center></a> </li>
<li><a href="dashboard.php?p=myfeed"><center>Account</center></a></li>
<li><a href="logout.php"><center>Log Out</center></a></li>
</ul>
</div>
<div class="main-content">
<div class="swipe-area"></div>
<a href="#" data-toggle=".container" id="sidebar-toggle">
<span class="bar"></span>
<span class="bar"></span>
<span class="bar"></span>
</a>
<div class="content">
<?php
session_start();
$sid = $_SESSION['ROWID'];
if(!$_SESSION){
header('location:index.php?msg=' . urlencode(base64_encode("Please login to continue.")));
}
else
{
$pages_dir = 'pages';
if(!empty($_GET['p'])){
$pages = scandir($pages_dir,0);
unset($pages[0],$pages[1]);
$p = $_GET['p'];
if(in_array($p.'.php',$pages))
{
include($pages_dir.'/'.$p.'.php');
}
else
{
echo "Sorry, page not found.";
}
}
}
?>
<p></p>
</div>
</div>
</div>
</body>
The problem occurs here in myfeed.php :
<?php
$user_name = "root";
$password = "";
$database = "rsswebapp";
$server = "127.0.0.1";
//$db_handle = mysqli_connect($server, $user_name, $password,$database);
$db_handle = new mysqli($server, $user_name, $password,$database);
$db_found = mysqli_select_db( $db_handle,$database);
//session_start();
// $s = mysql_real_escape_string($_SESSION['ROWID']);
//$query = "SELECT URL,TITLE FROM rssfeeds,user WHERE rssfeeds.USERID=user.ROWID";
// $query = "select GROUP_CONCAT(r.URL) as URL from user as u Left Join rssFeeds as r On u.ROWID = r.USERID WHERE USERID = $_SESSION";
// $query = "SELECT URL FROM rssfeeds as rss WHERE rss.USERID=$s";
// $query = "SELECT `rssfeeds`.`URL` FROM `rssfeeds` WHERE `rssfeeds`.`USERID`=$_SESSION['ROWID']";
// $query = "SELECT URL FROM rssfeeds as rss ,user as userrss WHERE rss.USERID='$sid'";
//$query = "SELECT rssfeeds.URL FROM rssfeeds LEFT JOIN user ON rssfeeds.USERID=user.ROWID";
$query = "SELECT URL FROM rssfeeds as rss WHERE rss.USERID='" . $_SESSION["ROWID"] . "'";
//$query = "SELECT URL FROM rssfeeds as rss,user WHERE rss.USERID="$_SESSION['ROWID']"";
// $result = mysqli_query($db_handle,$query);
$result = mysqli_query($db_handle,$query);
// $result = $db_handle->query($query);
$r = array();
$index = 0;
if($result)
{
echo "BG </br>";
// while($row = mysqli_fetch_assoc($result)) {
while($row = $result->fetch_array()){
// while( ($row = mysql_fetch_assoc($result))!== false){
//echo "WELL DONE";
echo "{$row["TITLE"]} {$row["URL"]} </br>" ;
// $r[$index] = $row["URL"];
// $index++;
// printf ("%s \n", $row["URL"]);
}
echo "HX";
/*
$yourArray = array();
$index=0;
while($row = $result->fetch_array()){
echo "b";
// echo "<a href='".$row["URL"]."'>".$row["TITLE"]."</a>";
$yourArray[$index] = $row;
$index++;
echo "<br />";print_r($yourArray);
}
*/
}
else
{
echo "HELLO";
die(mysql_error());
}
?>
The commented out statements are one which I have tried but did not work.I am not able to retrieve the URLs from the database table and display them on the page. As of now I get the output as :
BG HX
I have two tables in my database :
- user
- rssfeeds
'user' contains four columns namely :
- ROWID (auto-increment,primary)
- USERNAME
- PWD
whereas 'rssfeeds' has four :
- RSSFEEDID
- USERID
- TITLE
- URL
I am quite sure the problem lies in the query in myfeed.php in the $_SESSION part.
Upvotes: 0
Views: 374
Answers (3)
amaster
Reputation: 2163
New Answer Based upon new problem by OP:
Here are some pointers when debugging code and how I found the problems:
1) Add php error reporting to php files using the following code:
ini_set('display_errors',1);
error_reporting(E_ALL);
2) Echo within if and while/for loops to see if the condition was met
3) Echo var_dump() for queries when using variables to see what the actual query is that was ran like so:
$query = "SELECT TITLE, URL FROM rssfeeds as rss WHERE rss.USERID='" . $_SESSION["ROWID"] . "'";
echo var_dump($query);
$result = mysqli_query($db_handle,$query);
The fix is the following:
$num_rows = mysql_num_rows($qry);
if ($num_rows <= 0) {
//unset($_SESSION['uid']);
//unset($_SESSION['pwd']);
header('location:index.php?msg=' . urlencode(base64_encode("Sorry, there is no username $username with the specified password.Try again!")));
} else {
session_start();
$_SESSION['ROWID'] = $sid ;
header("location:dashboard.php");
}
should be the following: (You did not define $sid before nor actually retrieved the ROWID from the database $qry)
if ($num_rows!==1) {
header('location:index.php?msg=' . urlencode(base64_encode("Sorry, there is no username $username with the specified password. Try again!")));
} else {
$row = mysql_fetch_array($qry);
session_start();
$_SESSION['ROWID'] = $row['ROWID'] ;
header("location:dashboard.php");
}
Also, in myfeed.php:
$query = "SELECT URL FROM rssfeeds as rss WHERE rss.USERID='" . $_SESSION["ROWID"] . "'";
Should be the following: (Your were not retrieving the TITLE field though you were trying to call it later on in script.)
$query = "SELECT TITLE, URL FROM rssfeeds as rss WHERE rss.USERID='" . $_SESSION["ROWID"] . "'";
And lastly, your syntax for echoing the results was wrong. The following also in myfeed.php:
if($result)
{
echo "BG </br>";
// while($row = mysqli_fetch_assoc($result)) {
while($row = $result->fetch_array()){
// while( ($row = mysql_fetch_assoc($result))!== false){
//echo "WELL DONE";
echo "{$row["TITLE"]} {$row["URL"]} </br>" ;
// $r[$index] = $row["URL"];
// $index++;
// printf ("%s \n", $row["URL"]);
}
echo "HX";
Should be:
if($result){
echo "BG </br>";
while($row = $result->fetch_array()){
echo $row["TITLE"]." ".$row["URL"]." </br>" ;
}
echo "HX";
One last note, you are using mysql_* extension in login.php but are using mysqli_* extensions in myfeeds.php You should convert all instances of the deprecated extension to the new extensions. Most/All of the commented code can be removed as it is not needed as well. Test these changes and let me know how it works.
Upvotes: 0
amaster
Reputation: 2163
define your user_id that you want urls displayed for
$user_id = 1;
run simple non-joined query
SELECT
`rssfeeds`.`URL`
FROM
`rssfeeds`
WHERE
`rssfeeds`.`USERID`='$user_id'
If for some reason you needed data in the user table also then you could run this query
SELECT
`rssfeeds`.`URL`
FROM
`rssfeeds`
LEFT JOIN `user` ON (`rssfeeds`.`USERID`=`user`.`ROWID`)
WHERE
`user`.`ROWID`='$user_id'
OP:
I store the data in rssfeeds like this(image) .When I run this query:
SELECT rssfeeds.URL FROM rssfeeds WHERE rssfeeds.USERID=2;on phpmyadmin, I get something like this(image). What I don't understand is how to get this result in the form of individual links using PHP. I do something like this (image) Thanks.
$query = "SELECT URL, TITLE FROM rssfeeds as rss WHERE rss.USERID='".$_SESSION['ROWID']."'";
$result = mysql_query($query);
if($result){
while($row = mysql_fetch_assoc($result)){
echo "<a href='".$row["URL"]."'>".$row["TITLE"]."</a>";
echo "<br />";
}
}
Upvotes: 1
MilanPanchal
Reputation: 2953
USER TABLE:
CREATE TABLE `User` (
`ROWID` int(11) NOT NULL,
`USERNAME` varchar(45) DEFAULT NULL,
`EMAIL` varchar(45) DEFAULT NULL,
`PWD` varchar(45) DEFAULT NULL,
PRIMARY KEY (`ROWID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
RssFeeds TABLE:
CREATE TABLE `RssFeeds` (
`RssfeedId` int(11) NOT NULL,
`USERID` int(11) NOT NULL,
`TITLE` varchar(45) DEFAULT NULL,
`URL` text,
PRIMARY KEY (`RssfeedId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
RECORD IN USER TABLE
mysql> select * from User;
+-------+----------+-------+------+
| ROWID | USERNAME | EMAIL | PWD |
+-------+----------+-------+------+
| 1 | a | a | a |
| 2 | b | b | b |
| 3 | c | c | c |
+-------+----------+-------+------+
3 rows in set (0.00 sec)
RECORD IN RssFeeds TABLE
mysql> select * from RssFeeds;
+-----------+--------+-------+------+
| RssfeedId | USERID | TITLE | URL |
+-----------+--------+-------+------+
| 1 | 1 | t1 | u1 |
| 2 | 1 | t2 | u2 |
| 3 | 2 | t3 | u3 |
| 4 | 2 | t4 | u4 |
+-----------+--------+-------+------+
4 rows in set (0.00 sec)
RssFeeds By user
mysql> select u.*,r.URL from User as u Left Join RssFeeds as r On u.ROWID = r.USERID;
+-------+----------+-------+------+------+
| ROWID | USERNAME | EMAIL | PWD | URL |
+-------+----------+-------+------+------+
| 1 | a | a | a | u1 |
| 1 | a | a | a | u2 |
| 2 | b | b | b | u3 |
| 2 | b | b | b | u4 |
| 3 | c | c | c | NULL |
+-------+----------+-------+------+------+
5 rows in set (0.00 sec)
Using GROUP_CONCAT method
mysql> select u.*,GROUP_CONCAT(r.URL) as URL,GROUP_CONCAT(TITLE) as TITLE from User as u Left Join RssFeeds as r On u.ROWID = r.USERID GROUP BY u.ROWID;
+-------+----------+-------+------+-------+-------+
| ROWID | USERNAME | EMAIL | PWD | URL | TITLE |
+-------+----------+-------+------+-------+-------+
| 1 | a | a | a | u1,u2 | t1,t2 |
| 2 | b | b | b | u3,u4 | t3,t4 |
| 3 | c | c | c | NULL | NULL |
+-------+----------+-------+------+-------+-------+
3 rows in set (0.01 sec)
FOR PARTICULAR USER:
mysql> select u.*,GROUP_CONCAT(r.URL) as URL,GROUP_CONCAT(TITLE) as TITLE from User as u Left Join RssFeeds as r On u.ROWID = r.USERID WHERE ROWID = 1;
+-------+----------+-------+------+-------+-------+
| ROWID | USERNAME | EMAIL | PWD | URL | TITLE |
+-------+----------+-------+------+-------+-------+
| 1 | a | a | a | u1,u2 | t1,t2 |
+-------+----------+-------+------+-------+-------+
1 row in set (0.00 sec)
Upvotes: 0
Related Questions
- PHP with MySQL query not returning expected results
- SQL query not giving expected result
- Query not giving the expected results in PHP
- Why is this SQL query not returning the correct rows?
- SQL query is returning the wrong results
- SQL not Returning Desired Results
- sql statement not giving desired results
- php sql query not returning correct results
- sql query not returning correct results
- Not getting expected results from SQL query