MySQL Query????

tuxbuddy · April 16, 2008

Can I know how gonna I write query structure in PHP:
[code]
Query:
If ( Users.loginname =$myusername)         
Then 
$sql1 = SELECT users.id from users
--------------------------------------------------
$sql2="SELECT  incidents.id, incidents.description, users.firstname, users.surname, users.loginname
FROM incidents, users
WHERE ( 
incidents.owner_id = users.id
)
AND ( 
incidents.contact_id = $SQL1
)

I know the query is incorrect...But thats sample requirement.

My Main 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 (incidents.contact_id = users.id) AND ((select users.id from users) = ".$myusername.")"), $link))"), $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);
?>
                                                                                                                                                        



SELECT 

incidents.id, incidents.description, users.firstname, users.surname, users.loginname
FROM incidents, users
WHERE ( 

incidents.owner_id = users.id

)
AND ( 

incidents.contact_id =

[/code]

zenag · April 16, 2008

do u wnat to select userid for corresponding $myusername?????

Cep · April 16, 2008

What's your question?

tuxbuddy · April 16, 2008

Ya ...Zenag...Pls Chk ur mail too..

zenag · April 16, 2008

select category.id,details.categ from category,details where category.id=details.categ and category.id=2


$sql1 = mysql_query("SELECT id from users where loginname =$myusername");
$result=mysql_fetch_row($sql1 );
$uid=$result["id"];

$sql2="SELECT  incidents.id, incidents.description, users.firstname, users.surname, users.loginname
FROM incidents, users
WHERE ( 
incidents.owner_id = users.id
)
AND ( 
incidents.contact_id = $uid
)

zenag · April 16, 2008

dont use users.id (tablename.fieldname for selecting field from single table)like ...
$sql1 = SELECT users.id from users

tuxbuddy · April 17, 2008

Its not working !!1

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


<?php
session_start();

$myusername = $_SESSION['myusername'];
echo $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";
}

$sql1 = mysql_query("SELECT id from users where loginname =$myusername");


/* Passes a Query to the Active Database */
$result=mysql_fetch_row($sql1 );
$uid=$result["id"];

$result2 = mysql_query("SELECT incidents.id, incidents.description, users.firstname, users.surname, users.loginname FROM incidents,users WHERE (incidents.owner_id = users.id) AND  (incidents.contact_id = $uid)"), $link);

#$result = mysql_query("SELECT incidents.id,incidents.description,users.firstname,users.surname,users.loginname from incidents,users where (incidents.owner_id = users.id) OR (users.loginname = '$myusername')"), $link);




if (!$result2) {
  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>");
}

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

/* Closes Connection to the MySQL server */

mysql_close ($link);
?>

tuxbuddy · April 17, 2008

When I am running directly on SQL Server the query it is running successfully like this:

SELECT incidents.id, incidents.description, users.firstname, users.surname, users.loginname
FROM incidents, users
WHERE (
incidents.owner_id = users.id
)
OR (
incidents.contact_id = 'venkat'
) LIMIT 0 , 30

Query Results:

  
id description firstname surname loginname 
17 asdfzsvgfsd Ad. Administrator admin 
26 test1 Chandrappa Chikkanna chikkannac 
13 at Ad. Administrator admin 
24 test Ad. Administrator admin

But the same query when executed in PHP Code it says:

Report

venkat ID Description Owner Name Notes

hellovenkat

NOthing table it is displaying..

The modified code is:

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

$sql1 = mysql_query("SELECT id from users where loginname =$myusername");


/* Passes a Query to the Active Database */
$result=mysql_fetch_row($sql1 );
$uid=$result["id"];
echo "$uid";
$result2 = mysql_query("SELECT incidents.id, incidents.description, users.firstname, users.surname, users.loginname FROM incidents,users WHERE (incidents.owner_id = users.id) OR (incidents.contact_id = '".$uid."')", $link);

#$result = mysql_query("SELECT incidents.id,incidents.description,users.firstname,users.surname,users.loginname from incidents,users where (incidents.owner_id = users.id) OR (users.loginname = '$myusername')"), $link);




if (!$result2) {
  echo("<p>Error performing query: " . mysql_error() . "</p>");
  exit();
}

zenag · April 17, 2008

try   
echo "SELECT incidents.id, incidents.description, users.firstname, users.surname, users.loginname FROM incidents,users WHERE (incidents.owner_id = users.id) OR (incidents.contact_id = '".$uid."')";


after
$result2 = mysql_query("SELECT incidents.id, incidents.description, users.firstname, users.surname, users.loginname FROM incidents,users WHERE (incidents.owner_id = users.id) OR (incidents.contact_id = '$uid' ");

try that echoed query in mysql and note the result....

zenag · April 17, 2008

have u changed

$result as $result2 in 
while ($row = mysql_fetch_array($result2, MYSQL_ASSOC)) {

tuxbuddy · April 17, 2008

Its Not working .....look.

Issue still not fixd.

When We are writing the code:

echo "Please Contact Your Systems Administrator with the details";

}

$sql1 = mysql_query("SELECT id from users where loginname ='$myusername'");

echo "$sql1";

/* Passes a Query to the Active Database */

$result=mysql_fetch_row($sql1 );

$uid=$result["id"];

echo "$uid";

$result2 = mysql_query("SELECT incidents.id, incidents.description, users.firstname, users.surname, users.loginname FROM incidents,users WHERE (incidents.owner_id = users.id) OR (incidents.contact_id = '".$uid."')", $link);

Its just displaying:

Report

Resource id #3

ID Description Owner Name Notes

17 asdfzsvgfsd Ad.Administrator admin

26 test1 ChandrappaChikkanna chikkannac

13 at Ad.Administrator admin

24 test Ad.Administrator admin

Its not taking echo $uid .

I tried to print (look at red color)

How can I get $uid as id like 3,7,etc…not name …

Pls Help

zenag · April 17, 2008

do u mean

$result2 = mysql_query("SELECT incidents.id, incidents.description, users.firstname, users.surname, users.loginname FROM incidents,users WHERE (incidents.owner_id = users.id) OR (incidents.contact_id = '".$uid."')", $link);

doesnt select rows for (incidents.contact_id = '".$uid.") right?????

marrarp · April 17, 2008

I am with Tuxbuddy and just wanna make yu clear:

He means that :

The $uid as shown below:

$sql1 = mysql_query("SELECT id from users where loginname ='$myusername'");



echo "$sql1";

/* Passes a Query to the Active Database */

$result=mysql_fetch_row($sql1 );



$uid=$result["id"];

echo "$uid";

Is nt displaying anything.

zenag · April 17, 2008


try this...
while($result=mysql_fetch_row($sql1 ))
{



$uid=$result["id"];

echo "$uid";

}

tuxbuddy · April 17, 2008

No.....I think yu are not getting me ...

All I modified 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";
}

$sql1 = mysql_query("SELECT id from users where loginname ='$myusername'");

echo "$sql1";
/* Passes a Query to the Active Database */
while($result=mysql_fetch_row($sql1 ))
{

$uid=$result["id"];
echo "$uid";

}

$result2 = mysql_query("SELECT incidents.id, incidents.description, users.firstname, users.surname, users.loginname FROM incidents,users WHERE (incidents.owner_id = users.id) OR (incidents.contact_id

O/P:

Report

Resource id #3 ID Description Owner Name Notes

17 asdfzsvgfsd Ad.Administrator admin

26 test1 ChandrappaChikkanna chikkannac

13 at Ad.Administrator admin

24 test Ad.Administrator admin

Its same maan...I need $userid as numeric id not name..so that contact_id= 7 (say)

zenag · April 17, 2008

k i wl see it...

zenag · April 17, 2008

can u show me ur users database view in my mail

zenag · April 17, 2008

ru saying that $uid returns name other than numeric value??????.....

zenag · April 17, 2008

it cant be possible...check ur database "users" for 'id' fieldname .......

tuxbuddy · April 17, 2008

Check ur mails..

tuxbuddy · April 17, 2008

What I am attempting is OR (incidents.contact_id = '".$uid."')", the variable $uid should have ids like 1,2 etc…not name.

The Code:

$sql1 = mysql_query("SELECT id from users where loginname =' " .$myusername. " ' ");

echo "$sql1"; ----<<< Its displaying Resource #3

But echo $uid is displaying nothing….

Kindly check the code:

<title> Welcome to Project Management Tool</title>

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

}

$sql1 = mysql_query("SELECT id from users where loginname =' " .$myusername. " ' ");

echo "$sql1";

/* Passes a Query to the Active Database */

while($result=mysql_fetch_row($sql1 ))

{

$uid=$result["id"];

echo "$uid";

}

$result2 = mysql_query("SELECT incidents.id, incidents.description, users.firstname, users.surname, users.loginname FROM incidents,users WHERE (incidents.owner_id = users.id) OR (incidents.contact_id = '".$uid."')", $link);

#$result = mysql_query("SELECT incidents.id,incidents.description,users.firstname,users.surname,users.loginname from incidents,users where (incidents.owner_id = users.id) OR (users.loginname = '$myusername')"), $link);

if (!$result2) {

/* Starts the table and creates headings */

?>

<tr>

<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($result2, 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);

?>

zenag · April 17, 2008

what is the value of $myusername'??

tuxbuddy · April 17, 2008

The COde :

$sql1 = mysql_query("SELECT id from users where loginname ='".$myusername."'",$link);

echo "$sql1";

while($result=mysql_fetch_row($sql1 ))

{

echo "$result";

$uid=$result["id"];

echo "$uid";

is displaying:

Resource id #3Array

tuxbuddy · April 17, 2008

And if I modify it as :

$sql1 = mysql_query("SELECT id from users where loginname ='".$myusername."'",$link);

echo "$myusername";

echo "$sql1";

while($result=mysql_fetch_row($sql1 ))

{

echo "$result";

$uid=$result["id"];

echo "$uid";

}

Then The output gets displayed:

venkatResource id #3Array ID Description Owner Name Notes

17 asdfzsvgfsd Ad.Administrator admin

26 test1 ChandrappaChikkanna chikkannac

13 at Ad.Administrator admin

24 test Ad.Administrator admin

zenag · April 17, 2008

try this.................
$sql = mysql_query("SELECT id from users where loginname ='".$myusername."'",$link);


$row = mysql_fetch_row($sql);


echo $row[0];

Sign In

MySQL Query????

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information