Jump to content


  • Posts

  • Joined

  • Last visited


Profile Information

  • Gender
    Not Telling

wickning1's Achievements


Newbie (1/5)



  1. 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.
  2. 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); ?>
  3. 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.
  4. [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]
  5. 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());
  6. You need to fetch some results from $innerresult. Maybe $innerrow = mysql_fetch_array($innerresult);
  7. 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?
  8. 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.
  9. 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.
  10. 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]
  11. 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]
  12. 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.
  13. 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.
  14. You probably have a quoting problem. Show me more code surrounding that query.
  15. 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]
  • 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.