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

Nick Newman · 2009-07-20 04:06:39

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

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

Reines · 2009-07-20 08:44:50

What is the structure of the cashlogs table? I assume it has a from and to field that contains the users id, or from_id and to_id field as well as from_name and to_name?

Nick Newman · 2009-07-20 20:17:43

Reines wrote:

What is the structure of the cashlogs table? I assume it has a from and to field that contains the users id, or from_id and to_id field as well as from_name and to_name?

yes, the structure is:

Reines · 2009-07-20 22:37:09

Try replacing the current query with something like:

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

elbekko · 2009-07-21 00:50:24

Why on earth is everything there a text type?

A thousand DBAs just killed a puppy.

Smartys · 2009-07-21 01:58:46

I second Bekko:

Nick Newman · 2009-07-21 06:14:38

Smartys wrote:

I second Bekko:

Why would i make jesus cry? showing a screenshot? lol smarty u never help, u always just say something arrogant.. like that Reines (<3) === posted a query that i could use but u never do that, lol u dont like me or something ?

Last edited by Nick Newman (2009-07-21 06:15:36)

Nick Newman · 2009-07-21 06:16:42

elbekko wrote:

Why on earth is everything there a text type?
A thousand DBAs just killed a puppy.

Text is the best way, why? is that insecure?

Smartys · 2009-07-21 11:22:02

Nick Newman wrote:

elbekko wrote:
Why on earth is everything there a text type?
A thousand DBAs just killed a puppy.
Text is the best way, why? is that insecure?

I have to say, that's a great quote

Text is not the best way. Modern databases have established types for use in columns. Integers can be TINYINT, SMALLINT, INT, MEDIUMINT, BIGINT, etc. Smaller bits of text can be VARCHAR, CHAR, etc. By assigning a type (as opposed to shoving everything into TEXT columns) the database can be smarter about storage and about how the data is interpreted.

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.

Smartys · 2009-07-21 11:25:58

Nick Newman wrote:

Why would i make jesus cry? showing a screenshot? lol smarty u never help, u always just say something arrogant

Well, I was actually just agreeing with Elbekko. However, you obviously have forgotten the previous times you showed up and asked for "help"

http://punbb.informer.com/forums/search … k%20Newman

Reines · 2009-07-21 11:47:09

elbekko wrote:

Why on earth is everything there a text type?
A thousand DBAs just killed a puppy.

Jesus I didn't even notice that, just looked at the field names

MattF · 2009-07-21 12:03:26

Reines wrote:

Jesus I didn't even notice that, just looked at the field names

You obviously have an extrememly critical eye.

Reines · 2009-07-21 12:54:52

Nick Newman · 2009-07-21 19:13:38

Smartys wrote:

Nick Newman wrote:
elbekko wrote:
Why on earth is everything there a text type?
A thousand DBAs just killed a puppy.
Text is the best way, why? is that insecure?
I have to say, that's a great quote
Text is not the best way. Modern databases have established types for use in columns. Integers can be TINYINT, SMALLINT, INT, MEDIUMINT, BIGINT, etc. Smaller bits of text can be VARCHAR, CHAR, etc. By assigning a type (as opposed to shoving everything into TEXT columns) the database can be smarter about storage and about how the data is interpreted.
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.

Ok sir.. I changed those INT's

now what should i do with this:

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

You said now my query will be alot faster now because there not text

Thanks in advance Reine's!

elbekko · 2009-07-21 19:50:39

So you actually managed to make two out of four fields that shouldn't be an int into an int, but not those that actually should be. Congratulations.

Nick Newman · 2009-07-21 19:52:04

elbekko wrote:

So you actually managed to make two out of four fields that shouldn't be an int into an int, but not those that actually should be. Congratulations.

If u read his post it say only the 2 ones he calling from Database...

Tyler · 2009-07-21 20:01:33

Sorry to ruin the fun, but I felt like being nice.

Names should be varchars, not ints. Ints are numbers.

You want these as ints:
to_id
from_id
amount
time
page

These as varchars:
from_name
to_name
type

This as a text:
msg

Now, depending on what you're doing with type, it may be either an int or a varchar.

Last edited by Tyler (2009-07-21 20:02:05)

Nick Newman · 2009-07-21 20:54:23

Smartys wrote:

Nick Newman wrote:
elbekko wrote:
Why on earth is everything there a text
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.

NOw im confused on what tyler says?

Last edited by Nick Newman (2009-07-21 20:54:37)

Reines · 2009-07-21 21:12:17

It's joining on from_id and to_id not on from_name and to_name, you changed the wrong ones

What Tyler said is right.

Nick Newman · 2009-07-21 21:40:44

Ok so i changed them varchar, will the query look different? and if how so can u post it down, thanks tyler and Reines in advance.

Tyler · 2009-07-22 22:19:30

Nick Newman wrote:

Ok so i changed them varchar, will the query look different? and if how so can u post it down, thanks tyler and Reines in advance.

It shouldn't look different.

Nick Newman · 2009-07-23 06:33:22

Tyler wrote:

Nick Newman wrote:
Ok so i changed them varchar, will the query look different? and if how so can u post it down, thanks tyler and Reines in advance.
It shouldn't look different.

Why was slavok saying it will make db look slow?

Or does it just generally go faster bcz there not all texts? lol

Franz · 2009-07-23 16:35:26

Nick Newman wrote:

slavok

Try to stay in one forum/topic, please...

Nick Newman · 2009-07-23 19:22:26

lie2815 wrote:

Nick Newman wrote:
slavok
Try to stay in one forum/topic, please...

wat r u talking about? rofl

Reines · 2009-07-23 21:29:07

Slavok is part of PunBB, I assume you posted it on both PunBB and FluxBB forums. He hasn't replied here so no-one knows what you mean.

Forums

#1 2009-07-20 04:06:39

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

#2 2009-07-20 08:44:50

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

#3 2009-07-20 20:17:43

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

#4 2009-07-20 22:37:09

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

#5 2009-07-21 00:50:24

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

#6 2009-07-21 01:58:46

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

#7 2009-07-21 06:14:38

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

#8 2009-07-21 06:16:42

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

#9 2009-07-21 11:22:02

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

#10 2009-07-21 11:25:58

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

#11 2009-07-21 11:47:09

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

#12 2009-07-21 12:03:26

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

#13 2009-07-21 12:54:52

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

#14 2009-07-21 19:13:38

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

#15 2009-07-21 19:50:39

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

#16 2009-07-21 19:52:04

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

#17 2009-07-21 20:01:33

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

#18 2009-07-21 20:54:23

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

#19 2009-07-21 21:12:17

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

#20 2009-07-21 21:40:44

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

#21 2009-07-22 22:19:30

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

#22 2009-07-23 06:33:22

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

#23 2009-07-23 16:35:26

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

#24 2009-07-23 19:22:26

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

#25 2009-07-23 21:29:07

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

Board footer