creating a search using decimals...

[jbrill]

ok guys,

 

I have this form where u can search by job number through the database. the form is sent to a file.php where it is processed.

 

my problem is, sometimes jobs have different parts

a normal job # would be: 123

however sometimes there are parts to a job for example:

123

  123.1

  123.2

  123.3

 

I would like the search to be able to just type in 123 and it wil find everything with the exact match of 123 AHEAD of the decimal, and then just display them all like so:

123

123.1

123.2

123.3

 

 

[chigley]

Can you explain in more detail please? I'm a bit lost.. what's the maximum digit that can go after the decimal place? Please explain! :S

[akitchin]

you could use the BETWEEN operator:

 

SELECT stuff FROM table WHERE job_id BETWEEN 123 AND 123.9

 

this will only work if the column type is float.  alternatively you could use the LIKE operator, which is (IMO) a little less elegant:

 

SELECT stuff FROM table WHERE job_id LIKE '123%'

[jbrill]

ok basically, i need to be able to create a search function to look for job numbers.

there is a textbox on page1.php you type in the job number and it it is sent to page2.php where it is processed.

 

normal job numbers are in standard format: 123

but some job numbers have multiple parts to them, thus becoming:123, then 123.2 and 123.3 and so on.

lets put the maximum digit to 5

 

the search url that is passed to page2.php looks like this:

ttp://www.mydomain.com/admin/organize.php?keyword=123

 

the file then needs to grab that "123" in the url, look in the table 'jobs' where "job_number"='keyword'

 

and then display it in this format:

<table border="0" width="80%" bgcolor="#ffffff" class="MainBody1" cellpaddin="0" cellspacing="0" id="findjobs">
<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>

<?
if($_SESSION['type'] == "admin")
{

while($row = mysql_fetch_array($rs))
{

$css_class = ($css_class=='row_style_1') ? 'row_style_2' : 'row_style_1';

echo "<tr class=\"$css_class\">";

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><a class=\"link\" href=\"admin_modjob.php?idr=".$row['id']."&table=jobs\"><img src=\"images/actionEdit.gif\" border=\"0\"></a> <a class=\"link\" href=\"admin_delete.php?idr=".$row['id']."&table=jobs\"><img src=\"images/actionDelete.gif\" border=\"0\"></a></td>";
echo '</tr>';

echo '</table>';

 

[chigley]

I'd just use akitchin's first query then with a while() loop to output if I were you

[jbrill]

anyone else have any ideas? im pretty new to php, i will try the above code. I am still open to suggestions however

[jbrill]

hey guys, still don't have this solved... i would like to use the query above that was suggested, just dont know how i will make it work. here is my current query:

$query = "SELECT * FROM $myTable WHERE status = '$status' OR customer='$client' OR job_number='$keyword' OR id='$keyword'";