how do i display these queries? question from a noob..

[jbrill]

hey guys,

im working on a simple search function,  now that i have the correct way to gather all the variables, and then query the database, the only thing i cannot finish is how to echo the correct things. could someone please help me?

 

thanks so much!

here is the code for "organize.php"

<?
include 'admin_header.php';

// this part validates whether the user is logged in as a administrator or not
if($_SESSION['type'] != "admin")
{
echo "You are either not logged in, or logged in but with no authority to access this page.<br>please visit the log in page <a href=index.php>here</a>.";
}
else
{
include 'admin_jobmenu.php';

$status = (isset($_GET["status"])) ? $_GET["status"] : NULL;
$customer= (isset($_GET["customer"])) ? $_GET["customer"] : NULL;
$keyword= (isset($_GET["keyword"])) ? $_GET["keyword"] : NULL;

$myTable = "jobs";


$query = "";
$query = ($status != "" && $status != NULL) ? ($query = "") ? "WHERE status = '$status'" : " AND status = '$status'" : "";
$query = ($customer!= "" && $customer!= NULL) ? ($query = "") ? "WHERE name= '$customer'" : " AND name= '$customer'" : NULL;
$query = ($keyword!= "" && $keyword!= NULL) ? ($query = "") ? "WHERE keyword= '$keyword'" : " AND keyword= '$keyword'" : NULL;
$query = "SELECT * FROM $myTable " & $query;

?>

<table border="0" width="80%" bgcolor="#ffffff" class="MainBody1" cellpaddin="0" cellspacing="0">
<tr>
<td class="underline">Quote Number</td>
<td class="underline">Job Number</td>
<td class="underline">PO Number</td>
<td class="underline">Customer</td>
<td class="underline">Status</td>
<td class="underline">Action</td>
</tr>

<?
// this is where i need help... how do i display the folowing:
echo '<tr>';
echo '<td> ID GOES HERE</td>';
echo '<td> JOB NUMBER GOES HERE</td>';
echo '<td> PO NUMBER GOES HERE</td>';
echo '<td> NAME GOES HERE</td>';
echo '<td> STATUS GOES HERE</td>';
echo '<td>Action</td>';
echo '</tr>';

?>
</table>

<? include 'admin_footer.php'; ?>

<?
}
?>

[jbrill]

anyone?

[rameshfaj]

U can use mysql_num_rows($result);//where $result is the result from the query.

If the function returned a value greater than 0 than there is something that satisfies the query and u can manipulate the data as required.

[jbrill]

can you show me what you mean? thanks

[tapos]

$rs = mysql_query($query);
$row = mysql_fetch_array($rs);
echo '<tr>';
echo '<td> $row['id']//Your id column name</td>';
echo '<td> $row['job_num']//JOB NUMBER GOES HERE</td>';
.
.
.//so on
echo '</tr>';

 

--

Tapos Pal

[jbrill]

getting an error ont he fetch_array part....

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/morow/public_html/admin/organize.php on line 26

 

[jbrill]

here is the test code i sued when i got the error..

<?
include 'admin_header.php';

// this part validates whether the user is logged in as a administrator or not
if(isset($_SESSION['id']))
{
include 'admin_jobmenu.php';

$status = (isset($_GET["status"])) ? $_GET["status"] : NULL;
$customer= (isset($_GET["customer"])) ? $_GET["customer"] : NULL;
$keyword= (isset($_GET["keyword"])) ? $_GET["keyword"] : NULL;

$myTable = "jobs";


$query = "";
$query = ($status != "" && $status != NULL) ? ($query = "") ? "WHERE status = '$status'" : " AND status = '$status'" : "";
$query = ($customer!= "" && $customer!= NULL) ? ($query = "") ? "WHERE name= '$customer'" : " AND name= '$customer'" : NULL;
$query = ($keyword!= "" && $keyword!= NULL) ? ($query = "") ? "WHERE keyword= '$keyword'" : " AND keyword= '$keyword'" : NULL;
$query = "SELECT * FROM $myTable " & $query;
$rs = mysql_query($query);
$row = mysql_fetch_array($rs);
?>

<table border="0" width="80%" bgcolor="#ffffff" class="MainBody1" cellpaddin="0" cellspacing="0">
<tr>
<td class="underline">Quote Number</td>
<td class="underline">Job Number</td>
<td class="underline">PO Number</td>
<td class="underline">Customer</td>
<td class="underline">Status</td>
<td class="underline">Action</td>
</tr>

<?
// this is where i need help... how do i display the folowing:
echo '<tr>';
echo '<td>';
echo $row['id'];
echo '</td>';
echo '<td> JOB NUMBER GOES HERE</td>';
echo '<td> PO NUMBER GOES HERE</td>';
echo '<td> NAME GOES HERE</td>';
echo '<td> STATUS GOES HERE</td>';
echo '<td>Action</td>';
echo '</tr>';

?>
</table>

<? include 'admin_footer.php'; ?>

<?
}?>

 

[tapos]

Try this,

$query = "";
$query = ($status != "" && $status != NULL) ? ($query = "") ? "WHERE status = '$status'" : " AND status = '$status'" : "";
$query = ($customer!= "" && $customer!= NULL) ? ($query = "") ? "WHERE name= '$customer'" : " AND name= '$customer'" : NULL;
$query = ($keyword!= "" && $keyword!= NULL) ? ($query = "") ? "WHERE keyword= '$keyword'" : " AND keyword= '$keyword'" : NULL;
$query = "SELECT * FROM $myTable ". $query;
$rs = mysql_query($query);
$row = mysql_fetch_array($rs);

 

if not working then check your sql using print ($query) before calling mysql_query.

 

--

Tapos Pal

[jbrill]

ok.. i have one of the 3 search things working now. it is only working for the "keyword" field the "status" and "client" fields are not showing the results.

 

i did print ($query) and for keyword it worked great, how ever on the status and keyword ones it just says "select * from jobs" and then nothing after it...

 

here s my code:

<?
include 'admin_header.php';

// this part validates whether the user is logged in as a administrator or not
if(isset($_SESSION['id']))
{
include 'admin_jobmenu.php';

$status = (isset($_GET["status"])) ? $_GET["status"] : NULL;
$client= (isset($_GET["client"])) ? $_GET["client"] : NULL;
$keyword= (isset($_GET["keyword"])) ? $_GET["keyword"] : NULL;


$myTable = "jobs";


$query = "";
$query = ($status != "" && $status != NULL) ? ($query = "") ? "WHERE status = '$status'" : " WHERE status = '$status'" : "";
$query = ($client!= "" && $client!= NULL) ? ($query = "") ? "WHERE customer= '$client'" : " WHERE customer= '$client'" : NULL;
$query = ($keyword!= "" && $keyword!= NULL) ? ($query = "") ? "WHERE job_number= '$keyword'" : " WHERE job_number= '$keyword'" : NULL;
$query = "SELECT * FROM $myTable ". $query;
print ($query);
$rs = mysql_query($query);
$row = mysql_fetch_array($rs);?>

<table border="0" width="80%" bgcolor="#ffffff" class="MainBody1" cellpaddin="0" cellspacing="0">
<tr>
<td class="underline">Quote Number</td>
<td class="underline">Job Number</td>
<td class="underline">PO Number</td>
<td class="underline">Customer</td>
<td class="underline">Status</td>
<td class="underline">Action</td>
</tr>

<?
// this is where i need help... how do i display the folowing:
echo '<tr>';

echo '<td>';
echo $row['id'];
echo '</td>';

echo '<td>';
echo $row['job_number'];
echo '</td>';

echo '<td>';
echo $row['po_number'];
echo '</td>';

echo '<td>';
echo $row['customer'];
echo '</td>';

echo '<td>';
echo $row['status'];
echo '</td>';

echo '<td>Action</td>';
echo '</tr>';

?>
</table>

<? include 'admin_footer.php'; ?>

<?
}?>

 

 

[jbrill]

i need to figure this out any more ideas?

[Oldiesmann]

There are several problems with that code...

 

$query = "" - this will always set $query to an empty string.

Because you're not using ".=", $query will always get overwritten each time.

You can only have one WHERE keyword in a query - any other conditions must be appended using AND.

You can't just stick NULL in a query by itself.

 

It's relatively easy to fix all of those issues, but the fix mainly depends on whether or not only one variable will be set at a time.

[jbrill]

it doesn't matter if  more than one variable is set at a time, in fact i would prefer that.... all i'm trying to do is if there is no variable set, then don't query that part