Results 1 to 8 of 8

Thread: Help with MySql Query??

  1. #1
    Regular Member Travlin_Man's Avatar
    Join Date
    Mar 2002
    Location
    Michigan, USA
    Posts
    384

    Help with MySql Query??

    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

  2. #2
    Elite Member TonyT's Avatar
    Join Date
    Jan 2000
    Location
    Fairfax, VA
    Posts
    10,336
    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

  3. #3
    Regular Member Travlin_Man's Avatar
    Join Date
    Mar 2002
    Location
    Michigan, USA
    Posts
    384
    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[i].indexOf("#")!=0){ d.MM_p[j]=new Image; d.MM_p[j++].src=a[i];}}
    }
    function MM_swapImgRestore() { //v3.0
    var i,x,a=document.MM_sr; for(i=0;a&&i<a.length&&(x=a[i])&&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[i][n];
    for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=MM_findObj(n,d.layers[i].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[i]))!=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 --->&nbsp;&nbsp; <a href="viewsortinstruction.php?jobNumber=<?php echo $row_Recordset1['jobNumber']?>" target="_blank">Sort Instructions</a>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<a href="viewsortinformation.php?jobNumber=<?php echo $row_Recordset1['jobNumber']?>"target="_blank">Sort Information</a>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<a href="sortwarning.php?jobNumber=<?php echo $row_Recordset1['jobNumber'];?>"target="_blank">Sort Warning</a>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<a href="uploaddocs.php?jobNumber=<?php echo $row_Recordset1['jobNumber']?>" target="_blank">Documents</a>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<a href="../viewjob.php?jobNumber=<?php echo $row_Recordset1['jobNumber']; ?>" target="_blank">Cust View</a> &nbsp;&nbsp;&nbsp;&nbsp;<a href="../admin/editjob.php?jobId=<?php echo $row_Recordset1['jobId']; ?>">Edit Job</a>&nbsp;&nbsp;&nbsp;&nbsp;<a href="../admin/viewweek.php?jobId=<?php echo $row_Recordset1['jobId']; ?>">Weekly</a>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<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">&nbsp;</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">&nbsp;</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>&nbsp;&nbsp; <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>&nbsp;</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">&nbsp;</p>
    </body>
    </html>
    <?php
    mysql_free_result($Recordset1);
    ?>
    TOO MUCH GIDDYYUP,,, NOT ENOUGH WHOA!!!

    Trav

  4. #4
    Security Specialist greEd's Avatar
    Join Date
    May 2001
    Location
    Maryland
    Posts
    807
    Try:

    Code:
    $query_Recordset2 = "SELECT * FROM jobs WHERE jobcust LIKE 'PCC Inc' ORDER BY jobNumber+0 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

  5. #5
    Elite Member TonyT's Avatar
    Join Date
    Jan 2000
    Location
    Fairfax, VA
    Posts
    10,336
    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

  6. #6
    Regular Member Travlin_Man's Avatar
    Join Date
    Mar 2002
    Location
    Michigan, USA
    Posts
    384
    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

  7. #7
    no. no, No. NO, NO... NO! stevebakh's Avatar
    Join Date
    Aug 2002
    Posts
    3,228
    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.

  8. #8
    Regular Member Travlin_Man's Avatar
    Join Date
    Mar 2002
    Location
    Michigan, USA
    Posts
    384
    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

Similar Threads

  1. query? Web Patches - faster loading of Web Pages
    By madmimm in forum Broadband Tweaks Help
    Replies: 1
    Last Post: 03-21-08, 06:24 PM
  2. adsl+2 query
    By chipkoooo in forum General Broadband Forum
    Replies: 1
    Last Post: 12-03-07, 05:18 PM
  3. BT Broadband router query
    By Absconditus in forum General Broadband Forum
    Replies: 0
    Last Post: 05-08-07, 12:55 PM
  4. PHP MySQL dates
    By jorefice in forum Programming Forum
    Replies: 2
    Last Post: 05-07-07, 08:20 AM
  5. Replies: 0
    Last Post: 03-01-07, 12:53 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •