1 Topic by NiCk Newman (edited by NiCk Newman 2009-07-20 04:03)

  • Registered: 2008-02-15
  • Posts: 200

Topic: Cash logs, i made, i need to pull imgaward :)

here is my query that pull's up from my table "cashlogs"

$result = $db->query('SELECT * FROM cashlogs ORDER By time DESC LIMIT '.$start_from.', 25') or error('Unable to fetch log info', __FILE__, __LINE__, $db->error());

Here is a screenshot on what I pull smile

http://i32.tinypic.com/28u04xy.jpg

I would love it  so I can pull my field imgaward from the user's table and apply them to each from_name and to_name fields in my cashlogs table. smile Possible?

I am trying to act professional and nice as i can please help me on this what, thanks!

I am asking you for help because I don't want to use WHERE Statement. because then I Would have like 250 Querys.. lol

  • Registered: 2008-02-15
  • Posts: 200

Re: Cash logs, i made, i need to pull imgaward :)

http://i25.tinypic.com/s3imp3.jpg
That's my table structure if your interested.

  • Slavok
  • PunBB Developer
  • Offline
  • From: Russia
  • Registered: 2008-06-11
  • Posts: 1,238

Re: Cash logs, i made, i need to pull imgaward :)

To do this, you need to join 3 tables: cashlogs and 2 users tables (the first one is needed to get imgaward of the user with from_id, the second one - to get imgaward of the user with to_id). The php code with this sql query will look like:

$result = $db->query('SELECT c.*, u1.imgaward AS award_from, u2.imgaward AS award_to FROM '.$db->prefix.'cashlogs AS c INNER JOIN '.$db->prefix.'users AS u1 ON c.from_id = u1.id INNER JOIN '.$db->prefix.'users AS u2 ON c.to_id = u2.id ORDER BY time DESC LIMIT '.$start_from.', 25') or error('Unable to fetch log info', __FILE__, __LINE__, $db->error());

Now you can get awards of users via array keys 'award_from' and 'award_to'.

Slavok's Website

  • Registered: 2008-02-15
  • Posts: 200

Re: Cash logs, i made, i need to pull imgaward :)

Holy sht, 3 tables just for this? Slavok, thanks for posting the query! but dont u think there's a better way then not using 3 more queryes like what If i changed my tables from text to int field be better?

Thank u sir

  • Slavok
  • PunBB Developer
  • Offline
  • From: Russia
  • Registered: 2008-06-11
  • Posts: 1,238

Re: Cash logs, i made, i need to pull imgaward :)

NiCk Newman wrote:

but dont u think there's a better way then not using 3 more queryes like what If i changed my tables from text to int field be better?

There was only one query, which joined 3 tables. Not 3 queries. Sorry, I missed the link to your DB-scheme. The type "Text" doesn't do for most of the columns in your table. You need to optimize the structure of your table. I think, it should be:
from_name VARCHAR(200);
from_id INT(10) UNSIGNED;
page TINYINT(3);
amount FLOAT(5, 2);
to_name VARCHAR(200);
time INT(10) UNSIGNED;
type TEXT;
msg TEXT;
to_id INT(10) UNSIGNED;

Slavok's Website

  • Registered: 2008-02-15
  • Posts: 200

Re: Cash logs, i made, i need to pull imgaward :)

Slovak, thanks. I changed them all to that,  I Really appreciate your help.  Is it true now since I change it like that that my query will be less harder on the database and less joining tables is there a way please post that I am In search of that.. MY friend gave me this query but told me that If I changed all my stuff to 

from_name VARCHAR(200);
from_id INT(10) UNSIGNED;
page TINYINT(3);
amount FLOAT(5, 2);
to_name VARCHAR(200);
time INT(10) UNSIGNED;
type TEXT;
msg TEXT;
to_id INT(10) UNSIGNED;

then the query will look different.  this is the current query:

SELECT cl.*, f.imgaward AS from_imgaward, t.imgaward AS to_imgaward
FROM cashlogs AS cl
INNER JOIN users AS f ON f.id = cl.from_id
INNER JOIN users AS t ON t.id = cl.to_id
ORDER By cl.time
DESC LIMIT '.$start_from.', 25

7 Reply by MattF (edited by MattF 2009-07-22 19:43)

  • MattF
  • Senior Member
  • Offline
  • From: South Yorkshire, England
  • Registered: 2007-03-15
  • Posts: 1,956

Re: Cash logs, i made, i need to pull imgaward :)

The column(s) type has absolutely no bearing on the query. You have already had an explanation of what the column types mean.

MattF's Website

  • Registered: 2008-02-15
  • Posts: 200

Re: Cash logs, i made, i need to pull imgaward :)

MattF wrote:

The column(s) type has absolutely no bearing on the query. You have already had an explanation of what the column types mean.

Why u keep posting stuff that u dont understand? lol

Like I said in previous posts..

From Smartys

For instance, in your query, you join the users table twice. You join on two columns which are TEXT, which will make your query incredibly slow. Those columns should be INTs, just like their corresponding columns in the users table.


SO TEXT MAKES THEM SLOW HUH? REALLY? NOW I MADE THEM VARCHAR AND STUFF SHOULD THE QUERY BE THE SAME ? OR DOES IT JUST AUTOMATICALLY SPEED IT UP SINCE NOT ALL TEXT?S??

Thanks.

9 Reply by MattF

  • MattF
  • Senior Member
  • Offline
  • From: South Yorkshire, England
  • Registered: 2007-03-15
  • Posts: 1,956

Re: Cash logs, i made, i need to pull imgaward :)

NiCk Newman wrote:
MattF wrote:

The column(s) type has absolutely no bearing on the query. You have already had an explanation of what the column types mean.

Why u keep posting stuff that u dont understand? lol

[snipped]

THE QUERY BE THE SAME ? OR DOES IT JUST AUTOMATICALLY SPEED IT UP SINCE NOT ALL TEXT?S??

Thanks.

First, why are you asking as I obviously have no idea what I'm talking about?

Second, try as I might, I still find you an obnoxious, unhelpable little twat with a serious attitude problem and a total manners deficit, so just go screw yourself Nick. If you cannot retain some civility then you will receive absolutely none from me.

MattF's Website

10 Reply by NiCk Newman (edited by NiCk Newman 2009-07-22 21:02)

  • Registered: 2008-02-15
  • Posts: 200

Re: Cash logs, i made, i need to pull imgaward :)

*Content removed*

//Watch it NiCk Newman, Any more of that talk and your be banned. 1st Warning. /Utchin

11 Reply by Tieguy

  • Registered: 2008-03-02
  • Posts: 31

Re: Cash logs, i made, i need to pull imgaward :)

NiCk Newman wrote:
MattF wrote:

The column(s) type has absolutely no bearing on the query. You have already had an explanation of what the column types mean.

Why u keep posting stuff that u dont understand? lol

Like I said in previous posts..

From Smartys

For instance, in your query, you join the users table twice. You join on two columns which are TEXT, which will make your query incredibly slow. Those columns should be INTs, just like their corresponding columns in the users table.


SO TEXT MAKES THEM SLOW HUH? REALLY? NOW I MADE THEM VARCHAR AND STUFF SHOULD THE QUERY BE THE SAME ? OR DOES IT JUST AUTOMATICALLY SPEED IT UP SINCE NOT ALL TEXT?S??

Thanks.

It actually does speed up because of using the correct types.  MattF is right, you don't need to modify the query at all.

If you need any PunBB 1.2.* mods done, feel free to send me a PM; we can work out a price [if need be].
  • Registered: 2008-02-15
  • Posts: 200

Re: Cash logs, i made, i need to pull imgaward :)

Tieguy wrote:
NiCk Newman wrote:
MattF wrote:

The column(s) type has absolutely no bearing on the query. You have already had an explanation of what the column types mean.

Why u keep posting stuff that u dont understand? lol

Like I said in previous posts..

From Smartys

For instance, in your query, you join the users table twice. You join on two columns which are TEXT, which will make your query incredibly slow. Those columns should be INTs, just like their corresponding columns in the users table.


SO TEXT MAKES THEM SLOW HUH? REALLY? NOW I MADE THEM VARCHAR AND STUFF SHOULD THE QUERY BE THE SAME ? OR DOES IT JUST AUTOMATICALLY SPEED IT UP SINCE NOT ALL TEXT?S??

Thanks.

It actually does speed up because of using the correct types.  MattF is right, you don't need to modify the query at all.

thanks that's all i needed to know, and utchin plz come to my house