PHP Script for Database retrieval???

tuxbuddy · April 15, 2008

I have two database tables: users and incidents.

In incidents table I have the following fields:

id, description, date_reported,date_finished, date_started, owner_id,

In users table I have the following fields:

id,firstname,surname,email,password,loginname etc.

Now I have written a script with the following query:

SELECT incidents.id, incidents.description, incidents.date_reported, users.firstname,incidents.notes FROM incidents,users WHERE (incidents.owner_id = users.id) AND ( users.loginname = '$myusername')"

A user has just logged in and checking his calls.So I have added users.loginname='$myusername'.

Second pt of interest is users.firstname...Will it work? I have owner_id (in incidents table)which is equivalent to id( in users table).Will it be possible to retrieve the firstname.....thru users.firstname

Here's my complete code

<html>
<title> Welcome to Call Status Report</title>

<h1>Call Report</h1>

<?php
session_start();
//header("Content-Type: text/plain");


/* set's the variables for MySQL connection */

$server = "localhost:3306"; // this is the server address and port
$username = "root"; // change this to your username
$password = "mysql123"; // change this to your password

/* Connects to the MySQL server */

$link = @mysql_connect ($server, $username, $password)
or die (mysql_error());

/* Defines the Active Database for the Connection */

if (!@mysql_select_db("helpcore", $link)) {
    echo "<p>There has been an error. This is the error message:</p>";
    echo "<p><strong>" . mysql_error() . "</strong></p>";
    echo "Please Contact Your Systems Administrator with the details";
}

/* Passes a Query to the Active Database */

$result = mysql_query("SELECT incidents.id, incidents.description, incidents.date_reported, users.firstname,incidents.notes FROM incidents,users WHERE (incidents.owner_id = users.id) AND ( users.loginname = '$myusername')", $link);
if (!$result) {
 echo("<p>Error performing query: " . mysql_error() . "</p>");
 exit();
}

/* Starts the table and creates headings */
?>
<table border="1">
<tr>
<td><strong>ID</strong></td>

<td><strong>Description</strong></td>
<td><strong>Reported Date</strong></td>
<td><strong>Owner</strong></td>
<td><strong>Notes</strong></td>
</tr>
<?
/* Retrieves the rows from the query result set
and puts them into a HTML table row */

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {


    echo("<tr>\n<td>" . $row["incidents.id"] . "</td>");
   echo("<td>" . $row["incidents.description"] . "</td>");
   echo("<td>" . $row["incidents.date_reported"] . "</td>");
   echo("<td>" . $row["users.firstname"] . "</td>");
   echo("<td>" . $row["incidents.notes"] . "</td>");
}

/* Closes the table */
?>
</table>
<?

/* Closes Connection to the MySQL server */

mysql_close ($link);
?>

This Code is not working for me.Only it is displaying the table structure with no outputs.

GingerRobot · April 15, 2008

The only thing i can see immediately wrong is that $myusername appears to be undefined. Is this stored in a session?

tuxbuddy · April 15, 2008

Ya...Its Stored in a session.Its only displaying the ID ,DEscription,Firstname table structure but isnt taking from database:

My Attempts :

I tried putting it directly in PHPMYADMIN and it works there :

SELECT incidents.id, incidents.description, users.firstname
FROM incidents, users
WHERE (
incidents.owner_id = users.id
)
LIMIT 0 , 30

Its display:

id description firstname

17 asdfzsvgfsd Ad.

26 test1 Chandrappa

13 at Ad.

24 test Ad.

25 asdfasdg Ad.

But When I am entering the same as PHP Code like this:

.....
if (!@mysql_select_db("helpcore", $link)) {
     echo "<p>There has been an error. This is the error message:</p>";
     echo "<p><strong>" . mysql_error() . "</strong></p>";
     echo "Please Contact Your Systems Administrator with the details";
}

/* Passes a Query to the Active Database */

$result = mysql_query("SELECT incidents.id, incidents.description,users.firstname from incidents,users WHERE (incidents.owner_id = users.id)  LIMIT 0,30", $link);
if (!$result) {
  echo("<p>Error performing query: " . mysql_error() . "</p>");
  exit();
}

/* Starts the table and creates headings */
?>

Its simply displaying as :

ID Description Firstname

as table structure and nothing below that.

Is My COde Correct?

I just trying above removing the session variable but even after that it seems not to work.

My Complete Code (Just as example)

<html>
<title> Welcome to Project Management Tool</title>
<body bgcolor=ORANGE>
<h1> Report</h1>


<?php

//header("Content-Type: text/plain");


/* set's the variables for MySQL connection */

$server = "localhost:3306"; // this is the server address and port
$username = "root"; // change this to your username
$password = "mysql123"; // change this to your password

/* Connects to the MySQL server */

$link = @mysql_connect ($server, $username, $password)
or die (mysql_error());

/* Defines the Active Database for the Connection */

if (!@mysql_select_db("helpcore", $link)) {
     echo "<p>There has been an error. This is the error message:</p>";
     echo "<p><strong>" . mysql_error() . "</strong></p>";
     echo "Please Contact Your Systems Administrator with the details";
}

/* Passes a Query to the Active Database */

$result = mysql_query("SELECT incidents.id, incidents.description,users.firstname from incidents,users WHERE (incidents.owner_id = users.id)  LIMIT 0,30", $link);
if (!$result) {
  echo("<p>Error performing query: " . mysql_error() . "</p>");
  exit();
}

/* Starts the table and creates headings */
?>
<table border="1">
<tr>

/* Starts the table and creates headings */
?>
<table border="1">
<tr>
<td><strong>ID</strong></td>

<td><strong>Description</strong></td>
<td><strong>Firstname</strong></td>
</tr>
<?
/* Retrieves the rows from the query result set
and puts them into a HTML table row */

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {


     echo("<tr>\n<td>" . $row["incidents_id"] . "</td>");
    echo("<td>" . $row["incidents.description"] . "</td>");
    echo("<td>" . $row["users.firstname"] . "</td>");
  #  echo("<td>" . $row[""] . "</td>");
  #  echo("<td>" . $row["title"] . "</td>");
   # echo("<td>" . $row["name"] . "</td>");
   # echo("<td>" . $row["create_time"] . "</td>");
}

/* Closes the table */
?>
</table>
<?

/* Closes Connection to the MySQL server */

mysql_close ($link);
?>

conker87 · April 15, 2008

I don't think you need the table name and the field name in the arrays. Try:

<?php

     echo("<tr>\n<td>" . $row["incidents_id"] . "</td>");
    echo("<td>" . $row["idescription"] . "</td>");
    echo("<td>" . $row["firstname"] . "</td>");
  #  echo("<td>" . $row[""] . "</td>");
  #  echo("<td>" . $row["title"] . "</td>");
   # echo("<td>" . $row["name"] . "</td>");
   # echo("<td>" . $row["create_time"] . "</td>");

?>

GingerRobot · April 15, 2008

I can't see anything really wrong - are you sure your php setup supports the short tags? Try changing the <? for the full PHP opening tags - <?php

You also ought to close the table row inside your while loop - otherwise you're going to be getting a funny looking table. Change this line:

echo("<td>" . $row["users.firstname"] . "</td>");

To:

echo("<td>" . $row["users.firstname"] . "</td></tr>");

tuxbuddy · April 15, 2008

I tried modifying like this:

}

/* Passes a Query to the Active Database */

$result = mysql_query("SELECT incidents.id,incidents.description,users.firstname from incidents,users where (incidents.owner_id = users.id)", $link);
if (!$result) {
  echo("<p>Error performing query: " . mysql_error() . "</p>");
  exit();
}

The O/P it shows:

Report

ID Description Firstname

17 asdfzsvgfsd

26 test1

13 at

24 test

25 asdfasdg

Its not displaying the Firstname.

Does it seems we need to import firstname from users table.

Pls Help

tuxbuddy · April 15, 2008

I got it !!! To some extend...I rewrite the code as :

echo("<tr>\n<td>" . $row["id"] . "</td>");

echo("<td>" . $row["description"] . "</td>");

echo("<td>" . $row["firstname"] . "</td></tr>");

And it worked.

But when I added as :

$result = mysql_query("SELECT incidents.id,incidents.description,users.firstname,incidents.notes from incidents,users where (incidents.owner_id = users.id) AND ( users.firstname = '$myusername')", $link);

The Apache logs says:

?module_subdir=module_helpcore_public

[client 10.14.1.105] PHP Warning: session_start(): Cannot send session cache limiter - headers already sent (output started at /var/www/html/HelpCORE/module_helpcore_public/status.php:7) in /var/www/html/HelpCORE/module_helpcore_public/status.php on line 8, referer: http://10.14.2.36/HelpCORE/module_helpcore_public/incidents_show.php

[client 10.14.1.105] PHP Notice: Undefined variable: myusername in /var/www/html/HelpCORE/module_helpcore_public/status.php on line 33, referer: http://10.14.2.36/HelpCORE/module_helpcore_public/incidents_show.php

[client 10.14.1.105] PHP Warning: session_start(): Cannot send session cache limiter - headers already sent (output started at /var/www/html/fetch.php:7) in /var/www/html/fetch.php on line 8

[client 10.14.1.105] PHP Notice: Undefined variable: myusername in /var/www/html/fetch.php on line 33

[client 10.14.1.105] PHP Warning: session_start(): Cannot send session cache limiter - headers already sent (output started at /var/www/html/fetch.php:7) in /var/www/html/fetch.php on line 8

[client 10.14.1.105] PHP Notice: Undefined variable: myusername in /var/www/html/fetch.php on line 33

[client 10.14.1.105] PHP Warning: session_start(): Cannot send session cache limiter - headers already sent (output started at /var/www/html/fetch.php:7) in /var/www/html/fetch.php on line 8

[client 10.14.1.105] PHP Notice: Undefined variable: myusername in /var/www/html/fetch.php on line 33

What is correct way to write:

$username variables???

GingerRobot · April 15, 2008

1.) session_start() must be called before any output. Move it right to the top of your script.

2.) As i said earlier, $mysuername is undefined. If its in the session, extract it:

<?php
session_start();
$myusername = $_SESSION['myusername'];
//put the above right at the top of your script. Everything else can follow
?>

All the errors are caused by those two problems.

twsowerby · April 15, 2008

Pretty much what GingerRobot said,

This sort of thing should work...

<?php

//session start here
$username = "Bob"; //change this to the username in your session

//code below will select the right data for you based on the above variables.
$sql = "SELECT incidents.id, incidents.description, users.firstname FROM incidents, users WHERE incidents.owner_id = users.id AND users.firstname='$username'";
$query = mysql_query($sql);
while ($row = mysql_fetch_array($query))
{
$ID = $row["id"];
$desc = $row["description"];
$fname = $row["firstname"];
echo "$ID, $desc, $fname <br />";
}

?>

Hope that helps,

Tom

tuxbuddy · April 15, 2008

Great

Superb.....it worked.Thanks.

tuxbuddy · April 15, 2008

It seemed it worked..But SOme warning still prevails :

My Code:

<html>
<title> Welcome to Project Management Tool</title>
<body bgcolor=ORANGE>
<h1> Report</h1>


<?php
session_start();

$myusername = $_SESSION['myusername'];
//put the above right at the top of your script. Everything else can follow
?>

<?

//header("Content-Type: text/plain");


/* set's the variables for MySQL connection */

$server = "localhost:3306"; // this is the server address and port
$username = "root"; // change this to your username
$password = "mysql123"; // change this to your password

/* Connects to the MySQL server */

$link = @mysql_connect ($server, $username, $password)
or die (mysql_error());

/* Defines the Active Database for the Connection */

if (!@mysql_select_db("helpcore", $link)) {
    echo "<p>There has been an error. This is the error message:</p>";
    echo "<p><strong>" . mysql_error() . "</strong></p>";
    echo "Please Contact Your Systems Administrator with the details";
}

/* Passes a Query to the Active Database */

$result = mysql_query("SELECT incidents.id,incidents.description,users.firstname,users.surname,users.loginname from incidents,users where (incidents.owner_id = users.id) AND ( users.loginname = '$myusername')", $link);
if (!$result) {
 echo("<p>Error performing query: " . mysql_error() . "</p>");
exit();
}

/* Starts the table and creates headings */
?>
<table border="1">
<tr>
<td><strong>ID</strong></td>

<td><strong>Description</strong></td>
<td><strong>Owner Name</strong></td>
<td><strong>Notes</strong></td>

</tr>
<?
/* Retrieves the rows from the query result set
and puts them into a HTML table row */
echo "hello";
echo "$myusername";
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {


    echo("<tr>\n<td>" . $row["id"] . "</td>");
   echo("<td>" . $row["description"] . "</td>");
   echo("<td>" . $row["firstname"] . $row["surname"]. "</td>");
   echo("<td>" . $row["loginname"] . "</td></tr>");

  # echo("<td>" . $row["name"] . "</td>");
  # echo("<td>" . $row["create_time"] . "</td>");
}

/* Closes the table */
?>
</table>
<?

/* Closes Connection to the MySQL server */

mysql_close ($link);
?>

Its displaying Hello Venkat(session username) No issue till this.After That it shows table structure but no entries inside the table.

I checked the apache logs.Al t throws:

helpcore_public/incidents_show.php on line 8, referer: http://10.14.2.36/HelpCOR                                             E/menu.php?module_subdir=module_helpcore_public
[client 10.14.1.105] PHP Warning:  session_start(): Cannot send session cache li                                             miter - headers already sent (output started at /var/www/html/HelpCORE/module_he                                             lpcore_public/status.php:7) in /var/www/html/HelpCORE/module_helpcore_public/sta                                             tus.php on line 8, referer: http://10.14.2.36/HelpCORE/module_helpcore_public/in                                             cidents_show.php
[client 10.14.1.105] PHP Warning:  session_start(): Cannot send session cache li                                             miter - headers already sent (output started at /var/www/html/HelpCORE/module_he                                             lpcore_public/incidents_show.php:6) in /var/www/html/HelpCORE/module_helpcore_pu                                             blic/incidents_show.php on line 7, referer: http://10.14.2.36/HelpCORE/menu.php?                                             module_subdir=module_helpcore_public
[client 10.14.1.105] PHP Warning:  main(login_success.php): failed to open strea                                             m: No such file or directory in /var/www/html/HelpCORE/module_helpcore_public/in                                             cidents_show.php on line 8, referer: http://10.14.2.36/HelpCORE/menu.php?module_                                             subdir=module_helpcore_public
[client 10.14.1.105] PHP Warning:  main(): Failed opening 'login_success.php' fo                                             r inclusion (include_path='.:/usr/share/pear') in /var/www/html/HelpCORE/module_                                             helpcore_public/incidents_show.php on line 8, referer: http://10.14.2.36/HelpCOR                                             E/menu.php?module_subdir=module_helpcore_public
[client 10.14.1.105] PHP Warning:  session_start(): Cannot send session cache li                                             miter - headers already sent (output started at /var/www/html/HelpCORE/module_he                                             lpcore_public/status.php:7) in /var/www/html/HelpCORE/module_helpcore_public/sta                                             tus.php on line 8, referer: http://10.14.2.36/HelpCORE/module_helpcore_public/in                                             cidents_show.php
[client 10.14.1.105] PHP Warning:  session_start(): Cannot send session cache li                                             miter - headers already sent (output started at /var/www/html/HelpCORE/module_he                                             lpcore_public/incidents_show.php:6) in /var/www/html/HelpCORE/module_helpcore_pu                                             blic/incidents_show.php on line 7, referer: http://10.14.2.36/HelpCORE/menu.php?                                             module_subdir=module_helpcore_public
[client 10.14.1.105] PHP Warning:  main(login_success.php): failed to open strea                                             m: No such file or directory in /var/www/html/HelpCORE/module_helpcore_public/in                                             cidents_show.php on line 8, referer: http://10.14.2.36/HelpCORE/menu.php?module_                                             subdir=module_helpcore_public
[client 10.14.1.105] PHP Warning:  main(): Failed opening 'login_success.php' fo                                             r inclusion (include_path='.:/usr/share/pear') in /var/www/html/HelpCORE/module_                                             helpcore_public/incidents_show.php on line 8, referer: http://10.14.2.36/HelpCOR                                             E/menu.php?module_subdir=module_helpcore_public
[client 10.14.1.105] PHP Warning:  session_start(): Cannot send session cache li                                             miter - headers already sent (output started at /var/www/html/HelpCORE/module_he                                             lpcore_public/status.php:7) in /var/www/html/HelpCORE/module_helpcore_public/sta                                             tus.php on line 8, referer: http://10.14.2.36/HelpCORE/module_helpcore_public/in                                             cidents_show.php

Any idea?

Sign In

PHP Script for Database retrieval???

Recommended Posts

tuxbuddy

Link to comment

Share on other sites

GingerRobot

Link to comment

Share on other sites

tuxbuddy

Link to comment

Share on other sites

conker87

Link to comment

Share on other sites

GingerRobot

Link to comment

Share on other sites

tuxbuddy

Link to comment

Share on other sites

tuxbuddy

Link to comment

Share on other sites

GingerRobot

Link to comment

Share on other sites

twsowerby

Link to comment

Share on other sites

tuxbuddy

Link to comment

Share on other sites

tuxbuddy

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information