Jump to content

AV1611

Members
 View Profile  See their activity

  • Posts

    997

  • Joined

  • Last visited

 Content Type 

Posts posted by AV1611

    Can someone tell me

    in MySQL Help

    Posted

    I hope this is giving me the sum() of all records of the same Job AND Dept.

     

    I am getting multiple rows (one per Dept) with a DEPT_HOURS for each of those rows...

     

    I'm doing this correctly, right?

     

    Select distinct
labor.Dept AS DEPT,
labor.Job AS MS,
countsheet.PART_NUMBER,
Sum(labor.Total) AS DEPT_HOURS,
countsheet.TOTAL AS TOTAL_QTY,
countsheet.DUE AS UNCOMP_QTY,
countsheet.NET AS COMP_QTY
From
labor,countsheet
Where
labor.Job = countsheet.MS
Group By
labor.Job,
labor.Dept
Order By
countsheet.MS Asc
;

    How do you search databases?

    in MySQL Help

    Posted

    [!--quoteo(post=321689:date=Nov 24 2005, 05:45 AM:name=onemind)--][div class=\'quotetop\']QUOTE(onemind @ Nov 24 2005, 05:45 AM) 321689[/snapback][/div][div class=\'quotemain\'][!--quotec--]

    Hi,

     

    I have just about finished my site and have many tables filled with users, articles ect and am now trying to implement a search feature.

     

    Basicaly, i have a text box that the user enters keywords they want to search for then they click search.

     

    Is it just a matter of parsing the keywords then performing several select queries on the database or is there a proper way to search mysql databases?

     

    Any tips would be great.

     

    Thanks

     

    This is maybe overly simplistic, but,

    Select * from table where

    field1 like '%keyword1%' or

    field1 like '%keyword2%' or

    field1 like '%keyword3%' or

    field1 like '%keyword4%' or

    field1 like '%keyword5%' or

    field1 like '%keyword6%'

    ;

    Script is slowwww.

    in MySQL Help

    Posted

    I am doing a simple query that deletes all record with a date less than 2003-12-30...

     

    There are about 1.6 million records, and the query will expunge about 950,000 of them.

     

    The script seems to run forever, eventually, by computer says the query browser is no longer responding...

     

    Is it normal for a script like that to take over an hour? or do I have something configured wrong maybe?

     

    BTW, why doesn't this work?

    delete from test.labor where `DateNew` =< 1997-01-01;

    Need help constructing a Query

    in MySQL Help

    Posted

    You are my hero...

     

    [!--quoteo(post=321644:date=Nov 24 2005, 12:36 AM:name=ryanlwh)--][div class=\'quotetop\']QUOTE(ryanlwh @ Nov 24 2005, 12:36 AM) 321644[/snapback][/div][div class=\'quotemain\'][!--quotec--]

    does that relate to the other post? you should create a new field first, then do this:

    [!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']UPDATE[/span] table1 SET dateField=STR_TO_DATE(textField,'%c/%e/%Y'); [!--sql2--][/div][!--sql3--]

     

    Need help constructing a Query

    in MySQL Help

    Posted

    Any idea how I fix the dates?

     

     

    [!--quoteo(post=321640:date=Nov 24 2005, 12:06 AM:name=ryanlwh)--][div class=\'quotetop\']QUOTE(ryanlwh @ Nov 24 2005, 12:06 AM) 321640[/snapback][/div][div class=\'quotemain\'][!--quotec--]

    yeah, that's the query to get total hours. throw in the orders as needed.

     

    Need help constructing a Query

    in MySQL Help

    Posted

    First, I understand the date issue, we can leave that part off for now... I am not running PHP (But I can if I need to).

     

    I need to figure out the TOTAL hours for EACH job number... (There are several rows for each job number...

    Would this do it?

    Select sum(labor.Total),labor.`Date`,labor.Empno,labor.Dept,labor.Job
From labor Group by Job;

     

    HELP!

     

    [!--quoteo(post=321635:date=Nov 23 2005, 11:50 PM:name=ryanlwh)--][div class=\'quotetop\']QUOTE(ryanlwh @ Nov 23 2005, 11:50 PM) 321635[/snapback][/div][div class=\'quotemain\'][!--quotec--]

    your date field is stored like that??? that's not going to give you correct sorting result by date, and date comparison is going to be off (that's why mysql dates are yyyy-mm-dd). anyway

    [!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] Hours FROM table1 WHERE JobNumber='$number' AND date BETWEEN '1/1/2005' AND '11/1/2005' ORDER BY JobNumber ASC, Dept ASC [!--sql2--][/div][!--sql3--]

    as i mentioned the date format here is not going to give you what you want correctly

    Need help constructing a Query

    in MySQL Help

    Posted

    I have a table with the following field:

     

    JobNumber | EmployeeNo | Dept | Hours | Date

     

    123 | 1 | 200 | 1.5 | 1/1/2005

    123 | 2 | 200 | .75 | 1/2/2005

    222 | 2 | 200 | .25 | 3/7/2005

     

    There are 1.5 million lines of this...

     

    I need to write a query that does this:

     

    Show me ALL hours on a given JobNumber sorted by Job Number first, the Dept Second, and for all dates from 1/1/2005 to 11/1/2005

     

    I just can't seem to work it out...

    Thanks!

    simple question...

    in MySQL Help

    Posted

    Sorry, I have a habbit of answering my own question after I post them... I guess it's easier to see on the forum than in my text editor LOL

    yes, i was just doing that for example... sorry, i should have stuck with what you requested ;-)

     

    good luck

    300168[/snapback]

     

    simple question...

    in MySQL Help

    Posted

    Wouldn't that always return TRUE because you are comparing to todays date, not the date in the record?

     

    I was thinking the answer would be:

     

    [!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]

    [span style=\\\"color:#0000BB\\\"]<?php

    $Date[/span][span style=\\\"color:#007700\\\"]=[/span][span style=\\\"color:#0000BB\\\"]$_POST[/span][span style=\\\"color:#007700\\\"][[/span][span style=\\\"color:#DD0000\\\"]\'DATE\'[/span][span style=\\\"color:#007700\\\"]];

    [/span][span style=\\\"color:#0000BB\\\"]$sql [/span][span style=\\\"color:#007700\\\"]= [/span][span style=\\\"color:#DD0000\\\"]\\\"SELECT * FROM lbryperm WHERE TEK_PART_NUMBER LIKE \'$PartNo\' AND ROHS_PROCESS >= \'$DATE\';

    ?>[/span]

    [/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

     

    Would that be right?

    300163[/snapback]

    simple question...

    in MySQL Help

    Posted

    Wouldn't that always return TRUE because you are comparing to todays date, not the date in the record?

     

    I was thinking the answer would be:

     

    [!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]

    [span style=\"color:#0000BB\"]<?php

    $sql [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\"SELECT * FROM lbryperm WHERE TEK_PART_NUMBER LIKE \'$PartNo\' AND ROHS_PROCESS >= \"[/span][span style=\"color:#0000BB\"]$_POST[/span][span style=\"color:#007700\"][[/span][span style=\"color:#DD0000\"]\'DATE\'[/span][span style=\"color:#007700\"]][/span][span style=\"color:#DD0000\"]\";

    ?>[/span]

    [/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

     

    Would that be right?

     

    i would think you could simply do:

    [!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]

    [span style=\\\"color:#0000BB\\\"]<?php

    $sql [/span][span style=\\\"color:#007700\\\"]= [/span][span style=\\\"color:#DD0000\\\"]\\\"SELECT * FROM lbryperm WHERE TEK_PART_NUMBER LIKE \'$PartNo\' AND ROHS_PROCESS >= \'\\\" [/span][span style=\\\"color:#007700\\\"]. [/span][span style=\\\"color:#0000BB\\\"]date[/span][span style=\\\"color:#007700\\\"]([/span][span style=\\\"color:#DD0000\\\"]\'Y-m-d\'[/span][span style=\\\"color:#007700\\\"]) . [/span][span style=\\\"color:#DD0000\\\"]\\\"\'\\\"[/span][span style=\\\"color:#007700\\\"];

    [/span][span style=\\\"color:#0000BB\\\"]?>[/span]

    [/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

     

    this fills in the SQL format of the DATE field with todays date.

    300135[/snapback]

     

    simple question...

    in MySQL Help

    Posted

    Currently, the field ROHS_PROCESS contains YES or NO. We are going to change it to a date so we can plan on when the flag is set, in stead of it being manually set...

     

    I need the query to display on or after a date instead of the YES this it currently displays by...

     

    Please clarify... I'm confused.

    300113[/snapback]

     

     

     

    simple question...

    in MySQL Help

    Posted

    I need to change this to ROHS_PROCESS on or after a date...

     

    SELECT * FROM lbryperm

    WHERE TEK_PART_NUMBER

    LIKE '$PartNo'

    and

    ROHS_PROCESS LIKE 'yes'

     

    Sorry to be such a noob... Dates always mess me up

    csv to mysql conversion script

    in MySQL Help

    Posted

    Well, I guess you'd have to write a PHP script that puts the cvs into an array, then feed the array into the query... but they would have to always use the exact same format for the cvs...

     

    You can also give them access so they can only import to the table of your choice, and tell them not to screw it up...LOL

    (Sorry)

     

    As part of a website I'm developing, the website owner will need to put csv files on the web.  It would be much easier if I could firstly convert these files into mySQL.

     

    However, I'd like to make this as user friendly as possibly, so that they simply submit the csv file and it converts it to mySQL.

     

    Is there a script anyone knows of that does this?  I COULD give the users access to phpMyAdmin, but fear this could be a bit dangerous!

     

    Thanks,

    Ed Ludlow

    297238[/snapback]

     

    mysql QUERY error

    in MySQL Help

    Posted

    Are backticks valid for tablename? I thought they were for fieldnames...

     

    $table_name = $_GET['table_name'];
$query="SELECT * FROM '$table_name' ORDER BY id"; 
$result=mysql_query($query);
$num=mysql_numrows($result);

     

    Hi All,

    I have a variable which contains name of a Database Table:

    How can I pass this variable into the Query to access a data from the table, following are my code. I want to know whats wrong. I hope I am clear enough. I am using mySQL with PHP.

     

    $table_name = $_GET['table_name'];

     

    $query="SELECT * FROM `$table_name` ORDER BY id";

    $result=mysql_query($query);

    $num=mysql_numrows($result);

     

    Thanks.

    298218[/snapback]

     

    averaging fields

    in MySQL Help

    Posted

    no problem... take a look at this one, and see if you can grasp how the recurring

    the colors don't alternate...

     

     

    here is my code...

     

     

    <snip>

     

     

    <form METHOD="POST" action="surveyinsert.php">

     

    <?php

     

    <snip login stuff here>

     

    $result = mysql_query

    ("SELECT * FROM SURVEY");

     

    $color = '#ffffff';

     

    echo '<table cellspacing="3" BORDERCOLORLIGHT = "#5877B6" BORDERCOLORDARK = "#011233" align="center" border="5" width = "90%">';

     

     

    // these label the table fields on the form

    echo '<tr align="left" ><td width = "25%" align = "center" valign="middle" >

    Employee Name</td>

    <td width = "5%" align = "center" valign="middle" >

    ID</td>

    <td width = "14%" align = "center" valign="middle" >

    <h1>- -

    </td><td width = "14%" align = "center" valign="middle" >

    <h1>-

    </td><td width = "14%" align = "center" valign="middle" >

    Meets Expectations

    </td><td width = "14%" align = "center" valign="middle" >

    <h2>+

    </td><td width = "14%" align = "center" valign="middle" >

    <h2>+ +

    </td></tr>';

     

    while ($row = mysql_fetch_array($result))

    {

    $color = $color == '#ffffff' ? '#f2f3f4' : '#ffffff';

    echo '<tr style="background-color: $color;"><td>';

    echo $row['NAME'];

    echo '</td><td>';

    echo $row['ID'];

    echo '</td><td align = "center" ><input TYPE="RADIO" name="RATING" value="1"></td>

    <td align = "center" ><input TYPE="RADIO" name="RATING" value="2"></td>

    <td align = "center" ><input TYPE="RADIO" name="RATING" value="3"></td>

    <td align = "center" ><input TYPE="RADIO" name="RATING" value="4"></td>

    <td align = "center" ><input TYPE="RADIO" name="RATING" value="5"></td>

    </tr>';

    }

     

    echo "</table>";

    ?>

     

    <br>

    <center><input type="submit" name="submit" value="submit"></center>

    </form>

    averaging fields

    in MySQL Help

    Posted

    A little off topic, but part of this thread... then I can mark solved...

    <whining>

    My boss is quite unreasonable :x

    </whining>

    OK,

     

    Your table works great...

     

    I know how to add table color ,

     

    but... here is what he wants... every other row to be a different color, like the old color bar paper <showing my age>

     

    What I don't understand is query results are printed a row at a time and are recursive... I don't know how I would make the second row a different color with a query result... I know how to do it in a normal table...

     

    I hope I make sense...

     

    Thanks...

     

     

     

     

     

    [FIELD1]  [FIELD2]

    JOE              3

    JOE              4

    JOE              4

    JOE              3

    JOE              4

    I Want to make a query that displays the average number for records where field1=joe

     

    Or, do I need to use PHP to do the averaging?

    296938[/snapback]

     

    averaging fields

    in MySQL Help

    Posted

    You have a habit of helping me a log... Thanks...

     

    Using the second solution, can the results be echoed into a table?...

     

    whenever I try to echo any query into a table, it only gives me the first result, then the table closes, and I don't get any more... If I do it without the table, it works fine...

     

    I know, that's kind of a dumb question....

     

     

     

     

    the sql AVG() function should do the trick:

    [!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]

    [span style=\\\"color:#0000BB\\\"]<?php

    [/span][span style=\\\"color:#FF8000\\\"]// for specific user

    [/span][span style=\\\"color:#0000BB\\\"]$sql [/span][span style=\\\"color:#007700\\\"]= [/span][span style=\\\"color:#0000BB\\\"]mysql_query[/span][span style=\\\"color:#007700\\\"]([/span][span style=\\\"color:#DD0000\\\"]\\\"SELECT AVG(field2) AS avg WHERE field1 = \'joe\'\\\"[/span][span style=\\\"color:#007700\\\"]);

    echo [/span][span style=\\\"color:#0000BB\\\"]mysql_result[/span][span style=\\\"color:#007700\\\"]([/span][span style=\\\"color:#0000BB\\\"]$sql[/span][span style=\\\"color:#007700\\\"], [/span][span style=\\\"color:#0000BB\\\"]0[/span][span style=\\\"color:#007700\\\"], [/span][span style=\\\"color:#DD0000\\\"]\'avg\'[/span][span style=\\\"color:#007700\\\"]);

     

    [/span][span style=\\\"color:#FF8000\\\"]// for getting averages for all users:

    [/span][span style=\\\"color:#0000BB\\\"]$sql [/span][span style=\\\"color:#007700\\\"]= [/span][span style=\\\"color:#0000BB\\\"]mysql_query[/span][span style=\\\"color:#007700\\\"]([/span][span style=\\\"color:#DD0000\\\"]\\\"SELECT field1, AVG(field2) AS avg GROUP BY field1\\\"[/span][span style=\\\"color:#007700\\\"]);

    while ([/span][span style=\\\"color:#0000BB\\\"]$row [/span][span style=\\\"color:#007700\\\"]= [/span][span style=\\\"color:#0000BB\\\"]mysql_fetch_array[/span][span style=\\\"color:#007700\\\"]([/span][span style=\\\"color:#0000BB\\\"]$sql[/span][span style=\\\"color:#007700\\\"])) {

    [/span][span style=\\\"color:#0000BB\\\"]  echo [/span][span style=\\\"color:#DD0000\\\"]\\\"$row[field1] - $row[avg]<br />\n\\\"[/span][span style=\\\"color:#007700\\\"];

    }

    [/span][span style=\\\"color:#0000BB\\\"]?>[/span]

    [/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

     

    hope this helps!

    296969[/snapback]

     

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.