Help with MySql Query??

General software, Operating Systems, and Programming discussion.
Everything from software questions, OSes, simple HTML to scripting languages, Perl, PHP, Python, MySQL, VB, C++ etc.
Post Reply
User avatar
Travlin_Man
Regular Member
Posts: 384
Joined: Tue Mar 12, 2002 10:15 am
Location: Michigan, USA

Help with MySql Query??

Post by Travlin_Man »

This question is probably very easy, but I'm very new to this stuff.

I have a MySql databae set up at work loaded with over a years worth of information. One of the pages is a Jobs page and it carries information from several recordsets. The problem is the page is sorted or grouped by the actual job number. All was well until I het Job #1000. Instead of being at the top of the list it appears all the way down by Job #100.

SELECT *
FROM jobs
WHERE jobcust LIKE 'PCC Inc'
ORDER BY jobNumber DESC

How do I get Job #1000 to be at the top or better yet whay did it place it by Job #100?
TOO MUCH GIDDYYUP,,, NOT ENOUGH WHOA!!!

Trav
Top
User avatar
TonyT
SG VIP
Posts: 10356
Joined: Fri Jan 28, 2000 12:00 am
Location: Fairfax, VA

Post by TonyT »

Is jobNumber a medium int & auto-incremented field? You have a timestamp field in that table?
No one has any right to force data on you
and command you to believe it or else.
If it is not true for you, it isn't true.
LRH
Top
User avatar
Travlin_Man
Regular Member
Posts: 384
Joined: Tue Mar 12, 2002 10:15 am
Location: Michigan, USA

Post by Travlin_Man »

Job Number is entered manually. Here ill paste the page.

<?php
// Buzz inet PHPLS03 - Check User Session is set
session_save_path("/home/users/web/b594/ipw.pccsort/phpsessions");
session_start();
if(!isset($HTTP_SESSION_VARS['adminsession'])){
header("Location: /admin/index.php");
}
// Buzz inet PHPLS04 - Check User Level
session_save_path("/home/users/web/b594/ipw.pccsort/phpsessions");
session_start();
if(!isset($HTTP_SESSION_VARS['adminlevel'])){
header("Location: noaccess.php");
} else {
if($HTTP_SESSION_VARS['adminlevel'] < 3){
header("Location: noaccess.php");
}
}
require_once('../Connections/pccweb.php');
mysql_select_db($database_pccweb, $pccweb);
if($HTTP_SESSION_VARS['adminlevel'] < 9){
$query_Recordset1 = "SELECT * FROM jobs WHERE jobCust NOT LIKE 'PCC Inc' and jobNumber NOT LIKE '%A'
and jobNumber NOT LIKE '%B'
and jobNumber NOT LIKE '%C'
and jobNumber NOT LIKE '%D'
and jobNumber NOT LIKE '%E'
and jobNumber NOT LIKE '%F'
and jobNumber NOT LIKE '%G'
and jobNumber NOT LIKE '%H'
and jobNumber NOT LIKE '%I'
and jobNumber NOT LIKE '%J'
and jobNumber NOT LIKE '%K'
and jobNumber NOT LIKE '%L'
and jobNumber NOT LIKE '%M'
and jobNumber NOT LIKE '%N'
and jobNumber NOT LIKE '%O'
and jobNumber NOT LIKE '%P'
and jobNumber NOT LIKE '%Q'
and jobNumber NOT LIKE '%R'
and jobNumber NOT LIKE '%S'
and jobNumber NOT LIKE '%T'
and jobNumber NOT LIKE '%U'
and jobNumber NOT LIKE '%V'
and jobNumber NOT LIKE '%W'
and jobNumber NOT LIKE '%X'
and jobNumber NOT LIKE '%Y'
and jobNumber NOT LIKE '%Z'
ORDER BY jobNumber DESC";}
else {
$query_Recordset1 = "SELECT * FROM jobs inner join customers on jobs.jobCust = customers.custName WHERE jobs.jobCust NOT LIKE 'PCC Inc' ORDER BY jobs.jobNumber DESC";}
$Recordset1 = mysql_query($query_Recordset1, $pccweb) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

require_once('../Connections/pccweb.php');
mysql_select_db($database_pccweb, $pccweb);
$query_Recordset2 = "SELECT * FROM jobs WHERE jobcust LIKE 'PCC Inc' ORDER BY jobNumber DESC";
$Recordset2 = mysql_query($query_Recordset2, $pccweb) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);

function makeStamp($theString) {
if (ereg("([0-9]{4})-([0-9]{2})-([0-9]{2}) ([0-9]{2}) :( [0-9]{2}) :( [0-9]{2})", $theString, $strReg)) {
$theStamp = mktime($strReg[4],$strReg[5],$strReg[6],$strReg[2],$strReg[3],$strReg[1]);
} else if (ereg("([0-9]{4})-([0-9]{2})-([0-9]{2})", $theString, $strReg)) {
$theStamp = mktime(0,0,0,$strReg[2],$strReg[3],$strReg[1]);
} else if (ereg("([0-9]{2}) :( [0-9]{2}) :( [0-9]{2})", $theString, $strReg)) {
$theStamp = mktime($strReg[1],$strReg[2],$strReg[3],0,0,0);
}
return $theStamp;
}
function makeDateTime($theString, $theFormat) {
$theDate=date($theFormat, makeStamp($theString));
return $theDate;
}
require_once('../Connections/pccweb.php'); ?>
<html>
<head>
<title>PCC Inc. Jobs Database Admin</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript" type="text/JavaScript">
<!--
function MM_preloadImages() { //v3.0
var d=document; if(d.images){ if(!d.MM_p) d.MM_p=new Array();
var i,j=d.MM_p.length,a=MM_preloadImages.arguments; for(i=0; i<a.length; i++)
if (a.indexOf("#")!=0){ d.MM_p[j]=new Image; d.MM_p[j++].src=a;}}
}
function MM_swapImgRestore() { //v3.0
var i,x,a=document.MM_sr; for(i=0;a&&i<a.length&&(x=a)&&x.oSrc;i++) x.src=x.oSrc;
}
function MM_findObj(n, d) { //v4.01
var p,i,x; if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) {
d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);}
if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<d.forms.length;i++) x=d.forms[n];
for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=MM_findObj(n,d.layers.document);
if(!x && d.getElementById) x=d.getElementById(n); return x;
}
function MM_swapImage() { //v3.0
var i,j=0,x,a=MM_swapImage.arguments; document.MM_sr=new Array; for(i=0;i<(a.length-2);i+=3)
if ((x=MM_findObj(a))!=null){document.MM_sr[j++]=x; if(!x.oSrc) x.oSrc=x.src; x.src=a[i+2];}
}
//-->
</script>

<style type="text/css">
<!--
#apDiv1 {
position:absolute;
width:200px;
height:115px;
z-index:1;
border: 2;
background-color: #000000;
}
-->
</style>
</head>
<body onLoad="MM_preloadImages('../images/addjobroll.png','../images/adminroll.png','../images/sortroll.png','../images/timeroll.png','../images/jobcomroll.png','../images/defectroll.png','../images/custbillroll.png')">
<p align="center"><strong><font color="#0000FF" size="6"><em>PCC Inc. Jobs Database
Admin Page</em></font></strong></p>
<p align="center"><a href="addjob.php" onMouseOver="MM_swapImage('add1','','../images/addjobroll.png',1)" onMouseOut="MM_swapImgRestore()"><img src="../images/addjob.png" name="add1" width="125" height="40" border="0" id="add1"></a></p>
<p align="center"><font size="3"> <a href="admin.php" onMouseOver="MM_swapImage('admin1','','../images/adminroll.png',1)" onMouseOut="MM_swapImgRestore()"><img src="../images/admin.png" name="admin1" width="125" height="40" border="0" id="admin1"></a><font color="#FFFFFF">_<a href="sorting.php" onMouseOver="MM_swapImage('sort1','','../images/sortroll.png',1)" onMouseOut="MM_swapImgRestore()"><img src="../images/sort.png" name="sort1" width="125" height="40" border="0" id="sort1"></a>_<a href="time.php" onMouseOver="MM_swapImage('time1','','../images/timeroll.png',1)" onMouseOut="MM_swapImgRestore()"><img src="../images/time.png" name="time1" width="125" height="40" border="0" id="time1"></a>_<a href="comments.php" onMouseOver="MM_swapImage('jobcom1','','../images/jobcomroll.png',1)" onMouseOut="MM_swapImgRestore()"><img src="../images/jobcom.png" name="jobcom1" width="125" height="40" border="0" id="jobcom1"></a>_<a href="defects.php" onMouseOver="MM_swapImage('defect1','','../images/defectroll.png',1)" onMouseOut="MM_swapImgRestore()"><img src="../images/defect.png" name="defect1" width="125" height="40" border="0" id="defect1"></a>_<a href="billing.php" onMouseOver="MM_swapImage('custbill1','','../images/custbillroll.png',1)" onMouseOut="MM_swapImgRestore()"><img src="../images/custbill.png" name="custbill1" width="125" height="40" border="0" id="custbill1"></a></font></font></p>
<p align="center"><strong><font color="#0000FF" size="5"><em>Existing Jobs</em></font></strong></p>

<table border="" align="center" cellpadding="1" cellspacing="1" rules="rows" >
<td width="50" align="center"><div align="center"><strong>Job #</strong></div></td>
<td width="300"><div align="center"><strong>Description</strong></div></td>
<td width="100"><div align="center"><strong>Start Date</strong></div></td>
<td width="100"><div align="center"><strong>End Date</strong></div></td>
<td width="200"><div align="center"><strong>Primary Customer</strong></div></td>
<td width="200"><div align="center"><strong>Secondary Cust.</strong></div></td>
<td width="125"><div align="center"><strong>Manager</strong></div></td>
</tr>

<?php do { ?>
<tr>

<td width="70" rowspan="2" align="center" bgcolor="#CCCCCC"><font size="+2"><strong><?php echo $row_Recordset1['jobNumber']; ?></strong></font></td>
<td width="300" height="30" bgcolor="#CCCCCC"><div align="center"><?php echo $row_Recordset1['jobDesc']; ?></div></td>
<td width="100" bgcolor="#CCCCCC"><div align="center"><?php echo makeDateTime($row_Recordset1['jobStartdate'], 'M j, Y'); ?></div></td>
<td width="100" bgcolor="#CCCCCC"><div align="center"><?php if ($row_Recordset1['jobEnddate'] == "0000-00-00") {echo ?> <font color="#FF0000"> On-going </font>
<?php ;} else {echo makeDateTime($row_Recordset1['jobEnddate'], 'M j, Y');} ?>
</div></td>
<td width="200" bgcolor="#CCCCCC"><div align="center"><a href="edituser.php?custId=<?php echo $row_Recordset1['custId']; ?>" target="_blank"><?php echo $row_Recordset1['jobCust']; ?></a></div></td>
<td width="150" bgcolor="#CCCCCC"><div align="center"><?php echo $row_Recordset1['jobCust2']; ?></div></td>
<td width="125" bgcolor="#CCCCCC"><div align="center"><?php echo $row_Recordset1['jobManager']; ?></div></td>
</tr>

<tr bgcolor="#CCCCCC">
<td height="30" colspan="6" align="center" bgcolor="#CCCCCC"><?php echo $row_Recordset1['jobNumber']?> Forms and Links --->   <a href="viewsortinstruction.php?jobNumber=<?php echo $row_Recordset1['jobNumber']?>" target="_blank">Sort Instructions</a>     <a href="viewsortinformation.php?jobNumber=<?php echo $row_Recordset1['jobNumber']?>"target="_blank">Sort Information</a>     <a href="sortwarning.php?jobNumber=<?php echo $row_Recordset1['jobNumber'];?>"target="_blank">Sort Warning</a>     <a href="uploaddocs.php?jobNumber=<?php echo $row_Recordset1['jobNumber']?>" target="_blank">Documents</a>     <a href="../viewjob.php?jobNumber=<?php echo $row_Recordset1['jobNumber']; ?>" target="_blank">Cust View</a>     <a href="../admin/editjob.php?jobId=<?php echo $row_Recordset1['jobId']; ?>">Edit Job</a>    <a href="../admin/viewweek.php?jobId=<?php echo $row_Recordset1['jobId']; ?>">Weekly</a>     <a href="../admin/viewjob.php?jobId=<?php echo $row_Recordset1['jobId']; ?>">View Info</a> </td>
</tr>
<tr>
<td height="20" colspan="7"></td>
</tr>
<?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
</table>
<p align="center"> </p>
<p align="center"><strong><font color="#0000FF" size="5"><em>Existing PCC Jobs</em></font></strong></p>
<table width="85%" border="2" align="center" cellpadding="1" cellspacing="1" bordercolor="#000000">
<tr>
<td width="57"><div align="center">Job #</div></td>
<td width="122"><div align="center">Description</div></td>
<td width="53"><div align="center">Start Date</div></td>
<td width="49"><div align="center">End Date</div></td>
<td width="111"><div align="center">Primary Customer</div></td>
<td width="95"><div align="center">Secondary Cust.</div></td>
<td width="95"><div align="center">Manager</div></td>
<td width="45"><p align="center"> </p></td>
</tr>
<?php do { ?>
<tr>
<td height="30"><?php echo $row_Recordset2['jobNumber']; ?></td>
<td><?php echo $row_Recordset2['jobDesc']; ?></td>
<td><?php echo makeDateTime($row_Recordset2['jobStartdate'], 'M j, Y'); ?></td>
<td>
<?php if ($row_Recordset2['jobEnddate'] == "0000-00-00") {echo ?>
<font color="#FF0000"> On-going </font>
<?php ;} else {echo makeDateTime($row_Recordset2['jobEnddate'], 'M j, Y');} ?>
</td>
<td><?php echo $row_Recordset2['jobCust']; ?></td>
<td><?php echo $row_Recordset2['jobCust2']; ?></td>
<td><?php echo $row_Recordset2['jobManager']; ?></td>
<td><p><a href="/admin/editjob.php?jobId=<?php echo $row_Recordset2['jobId']; ?>">Edit
Job</a>   <a href="/admin/viewweek.php?jobId=<?php echo $row_Recordset2['jobId']; ?>">Weekly</a>
<a href="/admin/viewjob.php?jobId=<?php echo $row_Recordset2['jobId']; ?>">View
Info</a></p></td>
</tr>
<?php } while ($row_Recordset2 = mysql_fetch_assoc($Recordset2)); ?>
</table>
<p> </p>
<div align="center"></div>
<p align="center"><font size="3"><a href="admin.php" onMouseOver="MM_swapImage('admin11','','../images/adminroll.png',1)" onMouseOut="MM_swapImgRestore()"><img src="../images/admin.png" name="admin11" width="125" height="40" border="0" id="admin11"></a><font color="#FFFFFF">_<a href="sorting.php" onMouseOver="MM_swapImage('sort11','','../images/sortroll.png',1)" onMouseOut="MM_swapImgRestore()"><img src="../images/sort.png" name="sort11" width="125" height="40" border="0" id="sort11"></a>_</font><font size="3"><font color="#FFFFFF"><a href="time.php" onMouseOver="MM_swapImage('time11','','../images/timeroll.png',1)" onMouseOut="MM_swapImgRestore()"><img src="../images/time.png" name="time11" width="125" height="40" border="0" id="time11"></a></font></font><font color="#FFFFFF">_<a href="comments.php" onMouseOver="MM_swapImage('jobcom11','','../images/jobcomroll.png',1)" onMouseOut="MM_swapImgRestore()"><img src="../images/jobcom.png" name="jobcom11" width="125" height="40" border="0" id="jobcom11"></a>_<a href="defects.php" onMouseOver="MM_swapImage('defect11','','../images/defectroll.png',1)" onMouseOut="MM_swapImgRestore()"><img src="../images/defect.png" name="defect11" width="125" height="40" border="0" id="defect11"></a>_<a href="billing.php" onMouseOver="MM_swapImage('custbill11','','../images/custbillroll.png',1)" onMouseOut="MM_swapImgRestore()"><img src="../images/custbill.png" name="custbill11" width="125" height="40" border="0" id="custbill11"></a></font></font>
</p>
<p align="center"> </p>
</body>
</html>
<?php
mysql_free_result($Recordset1);
?>
TOO MUCH GIDDYYUP,,, NOT ENOUGH WHOA!!!

Trav
Top
User avatar
greEd
Posts: 807
Joined: Wed May 09, 2001 12:00 am
Location: Maryland

Post by greEd »

Try:

Code: Select all

$query_Recordset2 = "SELECT * FROM jobs WHERE jobcust LIKE 'PCC Inc' ORDER BY jobNumber[B]+0[/B] DESC";
I'm not sure if you have to remove the DESC after adding the +0, play with it a bit.
"I'm doing a (free) operating system (just a hobby, won't be big and professional...) for AT clones... It's not portable and it probably [won't ever] support anything other than AT hard disks, as thats all I have :-(." --Posted on Usenet August 1991 by Linus Trovalds
http://www.computerglitch.net
curiosity builds security | dd if=/dev/zero of=/dev/hda bs=512 count=100
EOF
Top
User avatar
TonyT
SG VIP
Posts: 10356
Joined: Fri Jan 28, 2000 12:00 am
Location: Fairfax, VA

Post by TonyT »

If the tables have a unique id field that's auto incremented then it makes it easy to order the query results numerically.
No one has any right to force data on you
and command you to believe it or else.
If it is not true for you, it isn't true.
LRH
Top
User avatar
Travlin_Man
Regular Member
Posts: 384
Joined: Tue Mar 12, 2002 10:15 am
Location: Michigan, USA

Post by Travlin_Man »

:D :D That did it Greed!!! +0 was the ticket. Thank you. Now if ya wanna teach this old dog a new trick,,, tell me why that worked.

Thanks again
TOO MUCH GIDDYYUP,,, NOT ENOUGH WHOA!!!

Trav
Top
User avatar
stevebakh
Posts: 3228
Joined: Wed Aug 07, 2002 12:00 pm

Post by stevebakh »

Travlin Man, I would guess it's because you're storing job numbers as a string in the table. Doing +0 will cast the value to an integer, which will then allow you to order numerically, though this may break in other instances. The original problem you described is perfectly normal when ordering strings.

You can see the datatypes for each field of the table by running describe jobs

Like Tony said, if your table has an auto incremented id field, it would be best to order using that.
Top
User avatar
Travlin_Man
Regular Member
Posts: 384
Joined: Tue Mar 12, 2002 10:15 am
Location: Michigan, USA

Post by Travlin_Man »

Thanks for the explanation stevebakh!!

Yes the table has a auto increment ID field called the JobID and it is the primary and I suppose I could use it to display my query, but when I started the DB I started with Job #100 and I figured it would get confusing if the ID didn't match the Job #. I have the jobNumber set as Unique. Plus I have some jobs#'s that I spit using letters and I have deleted jobs. I Know,,, prolly not the best way, but I am new to this and learning.. heh heh

I have a new question now though,,, check my new post..
TOO MUCH GIDDYYUP,,, NOT ENOUGH WHOA!!!

Trav
Top
Post Reply

Return to “Software Forum”