Extracting Data from Database?

russellbcv

I have attempted to start some coding from scratch from connecting to MySQL then selecting the table then executing the query and I do get some results but I'm missing a few columns?

http://www.rbcserver.net/test.php

Please can you take a look at link and let me know if you can see all the columns or are you missing any?

Please find my coding below>

<?php
$conn = @mysql_connect( "localhost", "russellbcv", "password" )
or die( "Could not connect" );
$rs = @mysql_select_db( "rates", $conn )
or die( "Could not select database" );
$sql = "select * from cdr";
$rs = mysql_query( $sql, $conn )
or die( "Could not execute query" );

$list = "<table border=\"1\" cellpadding=\"3\">";
$list .= "<tr><th>cli</th>";
$list .= "<th>username</th>";
$list .= "<th>date</th>";
$list .= "<th>time</th>";
$list .= "<th>number</th>";
$list .= "<th>destination</th>";
$list .= "<th>duration</th>";
$list .= "<th>cost</th></tr>";
while( $row = mysql_fetch_array( $rs ) )
{
$list .= "<tr>";
$list .= "<td>".$row["cli"]."</td>";
$list .= "<td>".$row["username"]."</td>";
$list .= "<td>".$row["date"]."</td>";
$list .= "<td>".$row["time"]."</td>";
$list .= "<td>".$row["number"]."</td>";
$list .= "<td>".$row["destination"]."</td>";
$list .= "<td>".$row["duration"]."</td>";
$list .= "<td>".$row["cost"]."</td>";
$list .= "</tr>";
}
$list .= "</table>";
echo( $list );
?> </body></html>

planetsim

Looks and works fine to me, please in future use [php][/php] tags when posting PHP makes it easier to read.

russellbcv

I have identified the problem!

It was to do with capital & lower case letters in those fields.

russellbcv

Now I can successfully extract the entire contents of the table is there anyway you can create a login script and when authenticated only that particular "username" records are extracted from the table?

russellbcv

I'm getting there!

On a successful login I can extract all the data from the table

If say I logged in as one of the users how can I filter out the username from within the tabale?

"username" is the 2nd column within the table

<?php $username = $POST['username'];
$password = $POST['password'];
$self = $SERVER['PHP_SELF'];
$referer = $SERVER['HTTP_REFERER'];

if( ( !$username ) or ( !$password ) )
{ header( "Location:$referer" ); exit(); }

$conn = @mysql_connect( "localhost", "russellbcv", "passworm" )
or die ( "Could not connect" );
$rs = @mysql_select_db( "my_database", $conn )
or die( "Could not select database" );
$sql="select * from users where user_name=\"$username\"
and password = password( \"$password\" )";
$rs = mysql_query( $sql, $conn )
or die( "Could not execute query" );
$num = mysql_numrows( $rs );

if( $num != 0 )

$conn = @mysql_connect( "localhost", "russellbcv", "passworm" )
or die( "Could not connect" );
$rs = @mysql_select_db( "rates", $conn )
or die( "Could not select database" );
$sql = "select * from cdr";
$rs = mysql_query( $sql, $conn )
or die( "Could not execute query" );

planetsim

Again as I said before please use [php][/php] tags when posting code.

There are two ways, firstly within the query, secondly in the loop in an if / else statement.

russellbcv

I can only find a method within the query of extracting all the data using

$sql = "select * from cdr";

I cannot find anywhere in the tutorials to show me how to filter?

russellbcv

$sql = "select * from cdr where username="$username"";

Am I on the right track?

leatherback

try like this:

$sql="select * from users where (user_name='$username')
and (password = '$password')"; 

$result = mysql_query($sql) or die(mysql_error() );

$numrows = mysql_num_rows($result);

if($numrows > 0) // found matching records
  {
while($row = mysql_fetch_array($result))
  {
  // produce output here
  }
}

else // No results

{
echo "I am sorry, but no users could be found in our records";
}

russellbcv

My login script is taking information from table = "users" and once authenticated redirected to table = "cdr"

How does this code extract just the user data and not the entire contents of that table? Within the table "cdr" I have included the username to filter on but I'm not sure to extract this infirmation?

leatherback

Hi Russelbc,

Since we cannot see your setup, and the exact information you nee d to extract / filter on, you have to look at the basic structure we show you.

In general a query is built up like this:

select SOMECOLUMS 
from SOMETABLE 
where SOMECOLUM = 'SOMEVALUE' 
order by SOMECOLUM

So, basically, if you have done the authentication, as I showed you before, you can then query the CDR table, and use some criterium to select information (SOMECOLUM = 'SOMEVALUE'). If that is the colum username in table cdr, which has to match with the submitted username, you would use:

"select * from cdr where username='$username'"

If you have the users stored in an other table, you should actually plase the user identification number, and not the name in your cdr table. This way, if the persons'name needs to be changed, you only ned to upodate one table.

russellbcv

Hi J

I have changed the the query to:-

$sql = "select * from cdr where username='$username'"

However, I get the following error..line 26 being the above.

Error Log>
[Sat Mar 26 11:29:36 2005] [error] PHP Parse error: syntax error, unexpected T_VARIABLE in
c:\Apache\htdocs\z_authenticate.php on line 26

russellbcv

Hi J

Could the problem be because the tables are in two different databases. At present my set-up is as follows:-

Database = "my_database" Table = "users"
Database = "rates" Table = "cdr"

$sql = "select * from cdr where username='$username'"

For the above to work do both tables need to be in the same database?

planetsim

Yes they do however it is possible to get them to work in different databases however its a little bit more difficuilt.

I'm getting there!

On a successful login I can extract all the data from the table

If say I logged in as one of the users how can I filter out the username from within the tabale?

$sql = "select * from cdr where username='$username'"

This is flawed. Basically your only selecting that record. If you want to select all records excluding the current username you must do somethnig similar to the following

Again without knowing table / database structure

SELECT * FROM mytbl WHERE username != '$mylogged_in_username'

[/code]

russellbcv

"This is flawed. Basically your only selecting that record. If you want to select all records excluding the current username you must do somethnig similar to the following"

I want to select all records associated to that particular user (logged in user) and ignore other users.

The format of my cdr table is as follows:-

cli
username *
date
time
number
destination
duration
cost

the username matches the login username in my "users" table.
i.e russellbcv