Jump to content


  • Posts

  • Joined

  • Last visited


Posts posted by mdnghtblue

  1. If you are doing a SELECT query to get a value, then doing a separate UPDATE query, then yes, you can have concurrent accesses change the value incorrectly.


    You should do this in a single UPDATE query. Post your code to get specific help with it.


    Also. you should not have a column that is based on values in other columns. This results in duplicated data. Any time you want to know freeland, just SELECT land - buildings


    I have to do two UPDATES because there are two users in different places, doing different things.


    I think I want to just scratch the freeland field and do the calculations, but I want to make sure it's worth it. Will doing the subtractions necessary to get the value take much (noticeably) longer than just accessing a database field?

  2. (using mysql 5.0) If two queries are sent to the database at the same time, trying to update the same field, what would happen? I think this is what my problem is, because I keep having discrepancies in certain fields.


    Say I have three fields:





    freeland must always equal land-buildings. And most of the time, it does. But every so often differences happen, and I think it's because two queries are trying to update the same fields at the same time. Is it possible that that's even the problem? Is there a way to fix it? (besides periodically recalculating)

  3. Don't you need to set address, city, zip_code, etc, to a value? Like this:


    UPDATE byrnjobdb.employees SET address='123', city='Kingsbury', zip_code='78638', home_phone='555-555-5555', active_employee='Yes', lic_rpls='No', lic_pe='No' WHERE employee_id=7


  4. Thank you so much, I'm pretty sure this works. Here's the query I used:


    delete bad_rows.* 
    from member as bad_rows 
           inner join ( 
               select companyname,street,city,state, min(id) as min_id 
               from member 
               group by companyname,street,city,state 
               having count(*) > 1 
           ) as good_rows on good_rows.companyname = bad_rows.companyname 
             and good_rows.min_id <> bad_rows.id;


    I tried it on really small tables and it worked. I'm trying it on a table with about 400,000 records and it's taken several hours already, guess I'm just gonna run it overnight.

  5. Sorry for the triple post but my modify button is missing...


    I'm trying to use this query to insert the distinct rows into the new table, and I get the ensuing error:


    mysql> insert into new_member select * from member where (select distinct companyname,city,state from member);
    ERROR 1241 (21000): Operand should contain 1 column(s)


    How do I fix this? I feel like the answer is so close. I want to copy all the data where companyname,city and state are unique.

  6. I've got a way to select the unique fields:


    SELECT DISTINCT companyname,city,state from member;


    So how do I go about deleting everything else?


    I found some articles that suggest creating a new table and moving the distinct data there. I just don't know how to combine the insert statement and select statement above.

  7. I've been trying to do this operation with a Java application, but I'm sure there's got to be a way to do this with mySQL.


    I have a database (with about 2.2 million records) with thousands of duplicate entries. I only want to check if it's a duplicate with three fields: companyname, city, and state. If more than one record in the database has that same information, then delete the extra records. How would I go about doing this? (hopefully in an efficient way, since it'll probably take a while)

  8. That was the problem. =) It was stripslashing it. Commented it out and it was fine.


    // replace ' with '' to avoid malformed SQL queries
    function sqlQuotes (&$str)
    $str = str_replace("'","''",stripslashes($str));


    Do I even still need this function if I'm using mysql_real_escape_string?


  9. This is probably an easy fix, but I'm still new to this stuff.


    I have a notice field in the database, and I save it like this:


    if ($save_notice) {
    $notice = htmlspecialchars(strip_tags($notice));
    //$notice = addslashes($notice);
    $notice = preg_replace("#([\S]{60})#i","\\1 ",$notice);
    $notice = substr(ereg_replace("\n\n","\n",$notice),0,1500);
    $users[notice] = $notice;
    echo "Notice Saved!<BR><b>Your Notice:</b> ".nl2br(htmlspecialchars_decode($notice))."<BR>";



    in saveUserData, I use mysql_real_escape_string before updating the database:


    $data = mysql_real_escape_string($data);
    $update .= "$tmp=\"$data\"";



    But the notice shows up with "rn"s where a line break should be (and in the database too). This only started happening when I added in mysql_real_escape_string. Am I using it wrong? =/

  10. $allies = mysql_query("SELECT num, clan FROM players WHERE clan=$users[clan];");
    $market = mysql_query("SELECT * FROM market WHERE time<=$time AND seller=$allies;"); // not sure what to put instead of $allies?


    How can I limit the second query to the sellers with the "num"s selected from the first query? I kinda doubt the way I set it up above works.

  11. Tricky...I played around with it a little, and with this:


    td.title { height: 0px; vertical-align:top; text-align:center; padding-bottom:0px;}


    in the style sheet, I was able to make the height go down a little bit, but not all the way. =/


    Also, you should probably work on being a little more consistent. Such as, including quotes around all your tag values, and making sure everything is lower-case. Goes for your style-sheet too, though I dunno if XHTML takes your style sheet into account (because XHTML shuns capitalization). But still, it'd be easier to follow if everything was consistent.


  • 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.