temporary tables

[greg252]

I need to create a temp table in mysql (using Php) select values out of another table and insert them into the temp table, then select a rand value out of the temp table.

I can do the insert select and the rand select, but what is the code for creating a temp table, I can't seem to find anything about it.

Thanks.

[Lars_Berg]

create temporary table magdur(c1 int,c2 int)

Why do you need the temp table? Can't you just add

order by rand limit 1

to your query?

[greg252]

Well,
I need to query 2 seperate tables, the first query has to have results appear 10 times more often than the 3rd query, the second query has to have results appear 5 times more often than the 3rd query and if no results from 1,2, or 3, I need to select rand from the 4th table.

So, what I figured I'd do is:
query the 1st table with conditions and insert the results 10x into a temp table, query the 1st table with different conditions and insert the results 5 x into temp table, query the 2nd table with conditions and put results in temp table.
Count the records of the temp table, if 0 select rand from the default table, if !=0, select rand from temp table.

[jeremuck]

out of extreme curiosity, what in the world is that for?

[greg252]

Banner rotation.

[Sxooter]

Hmmm. That seems the long way around.

I'd do this:

$t1 = count(*) from table 1;
$t2 = count(*) from table 2;
$t3 = count(*) from table 3;

# Then, get a random number:
# (don't forget to seed it, this is mostly pseudo-code ya know)

$r = rand(1,$t1+$t2+$t3);

# Check for the boundary:
if ($r<$t3){
  $table="table_3";
  $offset = rand(0,$t3-1);
} else if ($r>=$t3 && $r<$t2*5){
  $table="table_2";
  $offset=rand(0,$t2-1);
} else {
  $table="table_1";
  $offset=rand(0,$t1-1);
}

$query = "select * from $table limit 1 offset $offset";

Or the 1,$offset syntax that mysql uses.

[greg252]

Just finished the banner rotation function with the temp table, I have to say it's smooth and quick.

Takes 1 argument (the search term), doesn't return anything(echoes from within the function) and only took about 60 lines with the impressions calc. and update.

Don't know if I did it right, but I got it done.

[_Y_]

i also need to know how exactly temporary tables work

the way i see it, i need to generate 4 teporary tables and join them in to one select and generate that as output
errr, regarthless of that

how do u create temporary tables with php exactly?

10x in advance

[greg252]

run a query:
Create TEMPORARY table name (col_name type, col_name2 type)
The temp table then exists and you populate it with the data you need to.

Run the select on the temp table and output it.

If you are just querying 4 tables, doen't seem that you need temp table, just selects.
I needed a subset of data to run a rand() query on, which is why I went with the temp table.

good luck.
If you have any other questions, please ask.

[_Y_]

I just realized what my problem was

you have to run 1 query at the time in php, when using mysql_query

for every ; u need one mysql_query

that was all

i hope this helps someone as slow as me 😛

[greg252]

I ended up changing a few things.
First, the function now takes 2 arguments- the first is the zone or search term to find a relevant banner, the second is either 0 or the id of another banner on the page.
The function itself echos the code to display the banner, updates the impressions and returns the id of the banner.

Feelin good bout it.
If anyone has similar questions, please ask.