[query theory!]

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?

[query theory!]

(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)

[Unknown column 'admin' in 'where clause']

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

[Simple UPDATE question]

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

 

[[SOLVED] constructing a remove duplicate query]

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.

[[SOLVED] constructing a remove duplicate query]

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.

[[SOLVED] constructing a remove duplicate query]

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.

[Altering tables]

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';

[[SOLVED] constructing a remove duplicate query]

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)

[[SOLVED] mysql_real_escape_string]

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

[[SOLVED] mysql_real_escape_string]

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?

 

[[SOLVED] 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? =/

[[SOLVED] having trouble pulling multiple rows from a db]

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);

[search criteria from array]

Thanks very much. =)

[search criteria from array]

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 =/)

[search criteria from array]

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

[onmousedown doesn't seem to be working]

I tried this myself, and couldn't get it to work, even with onclick, in both Firefox and IE. Not sure what the problem is. =/

[Counting]

What does your UPDATE query look like?

[Help with Code]

Make a table with two columns?

[Changing data in a field without insert?]

mysql_query("UPDATE tablename SET crimeexp=crimeexp+12;")

 

Is that what you're looking for? *crosses fingers*

[[SOLVED] MYSQL UPDATE]

"postid" probably shouldn't have quotes around it:

 

mysql_query("UPDATE posts SET post = '$post' WHERE postid = $postid") or die("Could not edit post.");

 

See if that works.

[[SOLVED] sorting two-dimensional array]

It worked! =D Thanks very much.

[[SOLVED] sorting two-dimensional array]

Tried it, but it didn't work. After the array has been sorted, this code is executed:

 

while ($user = mysql_fetch_array($data))
{
$urank++;
if ($urank != $user[rank])
	dbquery("UPDATE $playerdb SET rank=$urank WHERE num=$user[num];");
}

 

I think the sorting worked though.

[table cell height]

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.

 

[table cell height]

Was it something like:

 

 // HTML
<td class="title" width="650">Title!</td>

 

and

 

 // CSS
td.title { height:20px; vertical-align:top; text-align:center;}

 

?