Need help with a PHP SQL query and extraction!

ftornell · 2012-08-15 21:37:01

Hi guys,
I need help to understand and get this to work.
I'm trying to lean php/mysql so this is a small script of mine.

I wanna crate a page for me so I can add my monthly costs.
Let's say I wanna know about my electricity and water each month.

So I created a table

CREATE TABLE testdata
(
type,
period,
cost
)

let's say I add type "1" for electricity, 2012-08-15 for period and 1500 for the cost.
and then add type "2" for water, 2012-08-15 for the period and then 1200 for the water cost.

Now I want to extract them info a table? and I wan't it to look something like this

Cost January February Mars...
Electricity 1500...
Water 1200...

for a table it would look

<table>
<tr>
<th>Cost</th>
<th>January</th>
<th>February</th>
</tr>
<tr>
<td>Electricity</td>
<td>1500</td>
<td>something</td>
</tr>
<tr>
<td>Water</td>
<td>1200</td>
<td>something</td>
</tr>
</table>

you get my point?

yaffare · 2012-08-16 07:00:04

<?php

$HOST = 'localhost';
$USERNAME = 'myuser';
$PASSWORD = 'mypass';
$DATABASE = 'mydatabase';

$connect = mysql_connect($HOST, $USERNAME, $PASSWORD);
if (!$connect) die("ERROR: mysql_connect; ".mysql_error()."\n");
if (!mysql_select_db($DATABASE, $connect)) die("ERROR: mysql_select_db; ".mysql_error($connect)."\n");

?>
<table>
<tr>
<th>Cost</th>
<th>January</th>
<th>February</th>
</tr>
<?php

$query = 'SELECT * FROM testdata';
$result = mysql_query($query, $connect);
while ($row = mysql_fetch_assoc($result))
{
  echo "<tr>\n";
  echo "<td>".$row['type']."</td>\n";
  echo "<td>".$row['period']."</td>\n";
  echo "<td>".$row['cost']."</td>\n";
  echo "</tr>\n";   
}
?>

</table>

Last edited by yaffare (2012-08-16 07:02:35)

ftornell · 2012-08-16 09:46:12

Hi,
Thx for helping me out.

Unfortenatily its not what I was wishing for.

the output now looks like:

Cost	January	February
1	2012-07-15	
2	2012-08-15	
1	2012-08-15

I was hoping for:

Cost January February Mars...
Electicity 1500 1000
Water 1200 800

The type 1 should match with electricity, that I can do using a simple variable $1 = Electricity (hmm or a variable cannot start with a number?)
But in the row for January I want to have the cost that is from that month where I now have a 'date' variable...

Last edited by ftornell (2012-08-16 09:52:10)

yaffare · 2012-08-17 10:26:54

ftornell, thx for your respond.

I think your exercise is a very good starting point for learning php.
With my script you have some kind of kickstart to go from.

You may wanna considering reading a book or do online research about learning php and programming in general.
Hope you will be able to pay your electricity bills till then:-)

ftornell · 2012-08-17 11:56:42

Hi,
I have read a few books and is constantly reading guides, w3school & php.net about these things but I cant really understand the information!

Any more hints?

drobole · 2012-08-17 13:19:40

ftornell wrote:

Hi,
...
The type 1 should match with electricity, that I can do using a simple variable $1 = Electricity (hmm or a variable cannot start with a number?)
...

The more common way to solve this is to use a lookup array:

...
$type_names = [1 => "Electricity", 2 => "Water",];
...

then, when you print the info to screen you can use the number you get from the database as a index into this array to get the name:

...
echo "<td>".$type_names[$row['type']]."</td>\n";
...

I haven't tested this but something like that should work

Last edited by drobole (2012-08-17 13:21:11)

drobole · 2012-08-17 13:42:39

Another thing you should be aware of is that a date is not a period.

Maybe you would be better off using a table like this:

CREATE TABLE testdata
(
type,
year,
month,
cost
)

Last edited by drobole (2012-08-17 13:44:22)

ftornell · 2012-08-17 21:12:00

drobole, thats genius!

Ill give it a go and will probably get back for more information and suggestions!

thx!

Last edited by ftornell (2012-08-17 21:13:08)

Arch Linux

#1 2012-08-15 21:37:01

Need help with a PHP SQL query and extraction!

#2 2012-08-16 07:00:04

Re: Need help with a PHP SQL query and extraction!

#3 2012-08-16 09:46:12

Re: Need help with a PHP SQL query and extraction!

#4 2012-08-17 10:26:54

Re: Need help with a PHP SQL query and extraction!

#5 2012-08-17 11:56:42

Re: Need help with a PHP SQL query and extraction!

#6 2012-08-17 13:19:40

Re: Need help with a PHP SQL query and extraction!

#7 2012-08-17 13:42:39

Re: Need help with a PHP SQL query and extraction!

#8 2012-08-17 21:12:00

Re: Need help with a PHP SQL query and extraction!

Board footer