wickning1

If all the extra table joins and whatnot are used to limit the data returned, they are definitely worth doing in one query. In my experience, you want to avoid returning more data to PHP than it absolutely needs. Now, if the joins are only adding more data, then it can be beneficial to break it up, but this depends on whether the database is local or remote, because initiating lots of new communications over a network can be very costly.

I would probably pull the first 800 characters or so and then trim any partial word at the end in PHP. <?php $descr = database_call(); echo preg_replace('/\s\S*$/', '', $descr); ?>

This will require MySQL 4.1 or greater. If you are using an older version, you will have to split it up into a couple queries. [code]UPDATE players p INNER JOIN (    SELECT t.id, MIN(t.position_id) as position_id, t.year    FROM temptable t    INNER JOIN (       SELECT id, year, MAX(games) as mgames       FROM temptable GROUP BY id, year    ) s ON s.mgames=t.games AND s.id=t.id AND s.year=t.year    GROUP BY t.id, t.year ) f ON p.id=f.id AND p.year=f.year SET p.position_id=f.position_id[/code] There remains one question of behavior - If I played shortstop and second base exactly 30 games each in 2005, which position goes in the players table? I set it up there to take the MIN position, so whichever position has a lower position_id will take preference.

[code]SELECT a.field1, a.field2, b.description as descr1, c.description as descr2, a.field3, a.etc FROM table2 a LEFT JOIN table1 b ON b.code=a.code1 LEFT JOIN table1 c ON c.code=a.code2[/code]

That's something you'll have to do on the PHP end.. write a function for it, then you can do something like this: $result = mysql_query(....) or email_me(mysql_error());

You need to fetch some results from $innerresult. Maybe $innerrow = mysql_fetch_array($innerresult);

It's a really bad idea to create new tables as a result of user actions. You should carefully rethink your design. What does your site do, what kind of information are users able to store?

Yeah I've seen that technique before. With holes in your data it is not truly random, and even without holes, it has clustering problems when you adapt it for more than one row. The two solutions I've considered are to fill in holes in your data, or create a nightly index table that's numbered sequentially and links to the primary key of the target table. With either of those, it's easy to generate random numbers in a given range and select them. The problem is keeping the data clean, and the overhead to do it. There really is no perfect way that I've ever seen to get random rows in mysql. The frustrating part is that it seems easy to build support for it into mysql itself. Maybe someday.

Filling in holes is a lot more reasonable than re-indexing, I've thought about it myself quite a few times, when I needed a fast way to select random rows (big holes make that a real pain). Unfortunately I've never really found a good way to fill in holes. The only thing I've ever even thought of is maintaining a table of integers - 1..max_records; and then LEFT JOIN + ORDER BY + LIMIT to find the smallest missing ids. I never needed it enough to implement something so ugly though.

If it's a database on the same server you can use the first connection, just specify database name in your query. For instance: [code]SELECT * FROM theotherdatabase.table[/code] You can also maintain two connections by keeping track of the resource returned by mysql_connect(). For instance: [code]$conn1 = mysql_connect(...FIRST DATABASE...); $conn2 = mysql_connect(...SECOND DATABASE...); $result = mysql_query(...QUERY FIRST DATABASE..., $conn1); $result2 = mysql_query(...QUERY SECOND DATABASE..., $conn2);[/code]

Yeah fenway's right, all those subqueries need their own parentheses: [code]INSERT INTO     tbl_Company     ( idcategoria , idsubcategoria , idcategoria2 , idsubcategoria2 , idcategoria3 , idsubcategoria3 , nomefantasia , endereco , numero , complemento , idestado , idcidade , idbairro , idregiao , cep , telefone , telefone2 , fax , tel0800 , palavrachave ) VALUES     ( (SELECT id FROM tbCategoria WHERE lcase(descricao)="esportes" LIMIT 1) ,     (SELECT id FROM tbSubCategoria WHERE lcase(descricao)="Academias de Ginástica" LIMIT 1) ,     , , , , "academia corpo e lazer" , "r. joão cancian" , "76" , "Sem Registro" ,     (SELECT id FROM tbEstados WHERE lcase(sigla)="sp" LIMIT 1) ,     (SELECT id FROM tbCidades WHERE lcase(cidade)="hortolândia" LIMIT 1) ,     (SELECT id FROM tbBairros WHERE lcase(bairro)="parque hortolândia" LIMIT 1) ,     (SELECT id FROM tbRegioes WHERE lcase(regiao)="sem região" LIMIT 1) ,     "" , "(19) 3865-1328" , "" , "" , "" , "academia ginástica;" )[/code]

Are you sure you want to use a LEFT JOIN? soilSampleID is set to NOT NULL so it will error out when the left joined column comes up NULL. Try an INNER JOIN and see if it does what you want it to. If not, you'll have to convert the NULL to a 0. I'll help you with that part if you ask.

mysql_query does NOT return the answer you are looking for. It returns a resource that contains lots of information about the query. To get a row of data out of the resource, you use a fetch function like mysql_fetch_array(). To get the second row, you use mysql_fetch_array() again. The third, again. Or you put it at the top of a loop.

You probably have a quoting problem. Show me more code surrounding that query.

That just doesn't look right to me. It's going to give wrong numbers. Isn't this more what you're looking for? [code]SELECT j.job_id, j.url_id, g.gross_income FROM client_job j LEFT JOIN (    SELECT job_id, SUM(dollar_amount) as gross_income    FROM client_job    WHERE client_job.remittance_date < CURDATE()    GROUP BY job_id ) g ON g.job_id = j.job_id[/code]

Well, the easy way is to use file() and explode() to parse the csv file, and then for each row send an update query to the database. If the CSV is a big one, you may want to do it in fewer queries. You can do that by creating and loading a temp table (one query), and updating the real table by joining with the temp table (one query). I wrote myself a function called massupdate() to do this automatically, but it's part of a package and doesn't stand alone very well, so I'm afraid I can't easily post it for you. You may want to try your hand at building one though, it comes in handy surprisingly often.

That was just a general example of how to print something like that, the $want_to_start_a_new_month was a stand-in for all the $month != $thismonth and $month = $thismonth stuff.

Not if two users log in from the same IP (public machine, dynamic IP, etc). Could certainly UNIQUE on user_id,ip_address though.

It makes a difference. You'll have to do it the way I showed you in my first response.

you can use the MySQL command "SHOW processlist" and then kill threads, but I wouldn't recommend it. Connections do close when PHP exits, unless you are using mysql_pconnect() or whatever the persistent connection function is. Also, PHP will not process for more than 30 seconds (or the setting in php.ini) before it exits as well. It is highly unlikely to have hanging threads these days, unless you specifically instruct them to hang. If you are getting "too many connections" errors, the most likely possibility is that you are opening more than one connection per page load, or some of your queries take too long, and so a few page loads at once throw you over the limit.

[code]$sql = 'SELECT f.id FROM user_flag f INNER JOIN user_data d ON d.id_group=f.id_group WHERE d.id = '.$int_id;[/code] Sounds a lot easier than what your code is doing. Am I missing something?

Good point, when he says different db it always makes me think different server. PHP has mysql_select_db() for switching, although sending a USE command through might work too.

It goes at the top of the loop, but put in a check so it doesn't put it in on the first time through the loop. [code]$firsttime = TRUE; while (...) {     if (!$firsttime && $want_to_start_a_new_month) { echo '</table></div></div>'; }     $firsttime = FALSE;     ... }[/code]

Not really a MySQL question, but of course I'll answer anyway. The clean way to do something like that is to make the upload handler in the included file a function, and have it return the new filename. That way your scoping all works out. You can also use a global variable, the included file sets it, the calling file checks its value later. The "global" construct can be used to bring it into scope. i.e. "global $cfg; echo $cfg;"

You can make as many connections as you want, but putting mysql_connect() inside a loop is a very bad idea. Just open two connections before the loop, and use whichever one you need to. The $connection variable is what stores all the information about the server, so you just need two of them: [code]$conn1 = mysql_connect(...first server...); $conn2 = mysql_connect(...second server...); $result = mysql_query("SELECT stuff", $conn1); while ($row = mysql_fetch_array($result) {     $innerresult = mysql_query("SELECT otherstuff", $conn2); }[/code]