mdnghtblue

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?

(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: land freeland buildings 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)

What does your table structure look like? It sounds like the admin column doesn't exist in that table...

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

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.

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.

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.

It would help to know if it gave you an error when trying to run the query, but try this: ALTER TABLE 'players' ADD 'jobcount' INT(11) default '3';

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)

Nice. Guess I'll take it out then. Thanks very much. =)

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?

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) { swearCheck($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; saveUserData($users,"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); sqlQuotes($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? =/

Not sure if this is the most efficient way, but this is how I'd do it: $mlistcontacts = mysql_query('SELECT email FROM emaillist WHERE emailID>0'); while($mlistcontacts = mysql_fetch_array ($mlistcontacts)) { $contacts[$i] = $mlistcontacts[email]; $i++; } echo implode(",", $contacts);

Thanks very much. =)

They're just in the same database. "seller" from market, and "num" from players are the same kind of data. (don't really know how to use relations in mySQL yet =/)