SQL Query not retrieving proper results

Question

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
EMAIL
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.

You'd need some `WHERE` clause here. Probably you don't even need to join the tables if you have the `USERID` stored in some variable after the login. — Eggplant, Jul 19 '13 at 14:26
@Eggplant I think doing an INNER JOIN is exactly the same as an = statement because the scheduler use it the same, isn't it? — David Level, Jul 19 '13 at 14:28
@DavidLevel It *the same* of what? I'm not talking about rewriting an `INNER JOIN` using a `WHERE` clause, rather using `WHERE rssfeeds.USERID = [some_id_here]`, which in this particular query makes the `JOIN` actually useless since you are not selecting any field from "user" table. — Eggplant, Jul 19 '13 at 14:31
@Eggplant :) just for good manners. I just so your answer I think you right, his join gets every user, not a specific one +1 — David Level, Jul 19 '13 at 14:35
@Eggplant I tried that previously.Here's the previous query: SELECT URL FROM rssfeeds as rss ,user as userrss WHERE rss.USERID=userrss.ROWID ; I was getting the same result as I am now.I tried to use PHP sessions but I don't have enough proficiency with PHP either. — Priyanshu Kumar, Jul 19 '13 at 16:43
@PriyanshuKumar The query you are looking for is `SELECT URL FROM rssfeeds WHERE USERID = ?;` but if you *don't have enough proficiency with PHP* to use a session variable to store the logged user, I would advice you to study the languange before you continue the project. Cheers. — Eggplant, Jul 19 '13 at 16:52
@Eggplant I cannot upvote your previous comment more than Once :( It deserves at least one hundred upvotes! Too many people want to do before they want to know. — amaster, Jul 19 '13 at 17:02
@Eggplant I tried this link's answer to transfer variable through sessions. However , I couldn't get it working. http://stackoverflow.com/questions/871858/php-pass-variable-to-next-page What I did was : on page 1 $_SESSION['ROWID'] = $sid ; on page 2 $sid = $_SESSION['ROWID']; then the query I mentioned in previous comment. — Priyanshu Kumar, Jul 19 '13 at 17:10
Having an id stored in a session variable doesn't make it automagically be used in a query where it doesn't even appear! The query you used was wrong, that is. Use this id in place of the `?` in this query `SELECT URL FROM rssfeeds WHERE USERID = ?`. And don't get offended, really, but you should learn about SQL and PHP before starting to code. For every minute invested in learning, you are actually saving 10 minutes of lost time afterwards, not to mention the quality of the resutls ;) — Eggplant, Jul 19 '13 at 17:17
@Eggplant Thanks for your help and advise.Actually its for a project of our choice,however I decided that it should be different from the others and hence just read a tutorial on W3schools in about a couple of days.I'm looking forward to learn PHP and SQL. — Priyanshu Kumar, Jul 19 '13 at 18:10
Please be aware that W3Schools has a lot of information, and some of what they have is just WRONG. Either they don't have the time, or they don't have the inclination to correct the misinformation, or they don't have a clue. [http://www.w3fools.com/](http://www.w3fools.com/) — spencer7593, Jul 29 '13 at 16:08

amaster · Answer 1 · 2013-07-24T22:15:34.703

1

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 />";
    }
}

edited Jul 24 '13 at 22:15

answered Jul 19 '13 at 14:28

amaster

1,915
5
25
51

What you are suggesting is not a dynamic and feasible solution.The user logs in to his account and there can be new registrants also.This would mean I keep changing my PHP file for every user. – Priyanshu Kumar Jul 19 '13 at 16:53
@PriyanshuKumar when the user logs in, save the user_id as `$_SESSION["user_id"]` then you can set the `$user_id=$_SESSION["user_id"]` I did not mean to hard code your user_id into `$user_id` I just thought you had enough comprehension for that part. – amaster Jul 19 '13 at 16:56
How to do that ? I set `$_SESSION["ROWID"] = $sid ;` on my login script after checking username and password matches from the database.Then I redirect the user to his dashboard page and this time I set `$sid = $_SESSION['ROWID'];` and check if session exists. If it does,I give him a list of links among which one is Feeds.If Feeds is clicked,I redirect him to a page with url something like :vproject/dashboard.php?p=myfeed where I fetch his feeds according to his id using query : `$query = "SELECT URL FROM rssfeeds as rss ,user as userrss WHERE rss.USERID='$sid'";`,however nothing is displayed. – Priyanshu Kumar Jul 23 '13 at 19:02
@PriyanshuKumar Try `$query = "SELECT URL FROM rssfeeds as rss WHERE rss.USERID='" . $_SESSION['ROWID'] . "'";` after of course you start the session using `session_start();` – amaster Jul 23 '13 at 20:51
I store the data in rssfeeds like this(image) : http://funkyimg.com/viewer.php?img=/2/4814/772/760465Snap_2013-07-24_at_23_20_03.png .When I run this `query = SELECT `rssfeeds`.`URL` FROM `rssfeeds` WHERE `rssfeeds`.`USERID`=2 ;`on phpmyadmin,I get something like this(image) : http://funkyimg.com/viewer.php?img=/2/4814/786/871959Snap_2013-07-24_at_23_37_17.png . 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) : http://funkyimg.com/viewer.php?img=/2/4814/802/994992Snap_2013-07-24_at_23_44_18.png Thanks. – Priyanshu Kumar Jul 24 '13 at 18:15
@PriyanshuKumar please see last edit to answer and see if that answers yoru question. Please also remember that you should use mysqli instead of mysql and when the oringal question changes to edit your question not comment. – amaster Jul 24 '13 at 22:16
Sorry I can't edit the question with images because of no reputation.Here's the changes I made as you had suggested(image) : http://funkyimg.com/viewer.php?img=/2/4817/915/756278Snap_2013-07-25_at_21_19_51.png I get no errors which is good,but I don't get any output also.Just a blank screen.Thanks for being so helpful anyways. – Priyanshu Kumar Jul 25 '13 at 15:52
Please help.I think that the query fetches the result correctly but there is a problem in acquiring data to PHP arrays.Also please clarify your edit,I tried it but it didn't work. – Priyanshu Kumar Jul 27 '13 at 18:56
@PriyanshuKumar there is not much else I can do for you without knowing what exactly is the problem you are having. The answer above should work for you. Could you `echo var_dump($row);` in place of `echo "".$row["TITLE"]."";` and let me know what you get? I suggest also reading http://php.net/manual/en/function.mysql-fetch-assoc.php if you have not recently/yet... – amaster Jul 27 '13 at 21:32
If I give you my code to read,will you be able to help me. I know I'm very close to getting this but I'm not seeing the problem clearly.And it's a small code of some 70 lines only.I haven't slept properly for the past three days because of this project.What do you think? – Priyanshu Kumar Jul 27 '13 at 22:14
@PriyanshuKumar I can and will help if you do the following... Edit this question and include ALL your current code from your login form page, login script, and dashboard.php including any include/require files... please be smart and replace all your database credentials with generic localhost/root/password I will test and debug for you. Please post copy/past code in your question be indenting every line with four spaces or a tab... pictures take more work to read and type than does copy paste – amaster Jul 28 '13 at 01:27
Done. Didn't replace database credentials though,sorry. – Priyanshu Kumar Jul 28 '13 at 21:09
@PriyanshuKumar sorry I was away from computer for most part of today... please see new answer on this question. – amaster Jul 29 '13 at 02:38

score 0 · Answer 2 · answered Jul 19 '13 at 14:42

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)

So you're suggesting an extra column in 'rssfeeds' table which should be made primary key ? — Priyanshu Kumar, Jul 19 '13 at 16:50
It will also work if you don't have `RssfeedId` column in `RssFeeds`table. But, for a simplicity every table have its own PK. Its not compulsory. — MilanPanchal, Jul 19 '13 at 17:04

amaster · Accepted Answer · 2013-07-29T02:45:45.310

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.

SQL Query not retrieving proper results

3 Answers3