[PHP/SQL] Data from multiple rows in one table

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

thiscatis
Forum Contributor
Posts: 434
Joined: Thu Jul 20, 2006 11:00 am

[PHP/SQL] Data from multiple rows in one table

Post by thiscatis »

Hi,

I'm having a problem creating a settings page.
I have a table in my database (cms_settings) that pretty much looks like this:

setting_id......setting.......value
--------------------------------------
1......................name.........Simon
2......................zip.............35800
3......................phone........4867878

etc..

I know how to display all of the values.
But how can I generate a page that ,for example, only
displays the values of setting 1 & 3?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Add a WHERE clause to your sql statement that only selects some records.
see http://www.w3schools.com/sql/sql_where.asp
thiscatis
Forum Contributor
Posts: 434
Joined: Thu Jul 20, 2006 11:00 am

Post by thiscatis »

But how do you point your variables to look to the right row then?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

I don't understand the question.
printf
Forum Contributor
Posts: 173
Joined: Wed Jan 12, 2005 5:24 pm

Re: [PHP/SQL] Data from multiple rows in one table

Post by printf »

thiscatis wrote:Hi,

I'm having a problem creating a settings page.
I have a table in my database (cms_settings) that pretty much looks like this:

setting_id......setting.......value
--------------------------------------
1......................name.........Simon
2......................zip.............35800
3......................phone........4867878

etc..

I know how to display all of the values.
But how can I generate a page that ,for example, only
displays the values of setting 1 & 3?
Something like...

Code: Select all

SELECT * FROM table WHERE setting_id IN( 1, 3 );

printf
thiscatis
Forum Contributor
Posts: 434
Joined: Thu Jul 20, 2006 11:00 am

Post by thiscatis »

if I have:

question1

edit// so I have:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE setting_id IN( 1, 3 );") or die(mysql_error());  
					  while($row = mysql_fetch_array( $result )) {

// what do I have put here to get the content of the values of setting 1 & 3?}
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Most probably the same code as for SELECT * FROM table
thiscatis wrote:I know how to display all of the values.
shouldn't be a problem for you then.
thiscatis
Forum Contributor
Posts: 434
Joined: Thu Jul 20, 2006 11:00 am

Post by thiscatis »

recieved the aha-feeling now ;)
thiscatis
Forum Contributor
Posts: 434
Joined: Thu Jul 20, 2006 11:00 am

Post by thiscatis »

hmm still no luck..

i now have:

Code: Select all

$result = mysql_query("SELECT value FROM cms_settings WHERE setting_id IN( 1, 3 )") or die(mysql_error());  
					  while($row = mysql_fetch_array( $result )) {


//?

						   }
I've tried some bits of code there (//?) but I can't get my variables right.
What's the php code there to site e.g

$siteowner = the value of row1
$zipcode = the value of row3
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Code: Select all

$result = mysql_query("SELECT `setting`, `value` FROM cms_settings WHERE setting_id IN( 1, 3 )") or die(mysql_error()); 

$config = array();
while($row = mysql_fetch_assoc( $result )) {
   $config[$row['setting']] = $row['value'];
} 

echo '<pre>';
print_r($config);
Basically, store your configuration variables in an array, or if you absolutely don't want to use an array use variable variables
thiscatis
Forum Contributor
Posts: 434
Joined: Thu Jul 20, 2006 11:00 am

Post by thiscatis »

Hmm, tried it with the array but didn't get the results i wanted.
I tried using the config[0] to get the first result of the array but it didn't work.
How can I echo parts of the array I want?
(in other words, setting a variable to a certain value of the array)
Sorry for my bad english
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

the print_r($config) will show you the keys in your array..

access them by $config['key_name_here']
thiscatis
Forum Contributor
Posts: 434
Joined: Thu Jul 20, 2006 11:00 am

Post by thiscatis »

Thanks!
Working now;)

Another question, don't want to open a new topic for this one



is there a better way to achieve this:

Code: Select all

case 'Settings': 
		
		if($_POST['submit']) {
		
   $sitename = $_POST['sitename'];
   $siteurl = $_POST['siteurl'];
   $siteowner = $_POST['siteowner'];
   $rootemail = $_POST['rootemail'];
   $rootpass = $_POST['rootpass'];
   $rootadress = $_POST['rootadress'];
   $rootzip = $_POST['rootzip'];
   $rootcity = $_POST['rootcity'];
   $rootphone = $_POST['rootphone'];
   $rootpaypal = $_POST['rootpaypal'];
   $rootbankwire = $_POST['rootbankwire'];
   $rootcountry = $_POST['rootcountry'];
   
    $result = mysql_query("UPDATE cms_settings SET value='$sitename' WHERE setting_id=1");
	$result = mysql_query("UPDATE cms_settings SET value='$siteurl' WHERE setting_id=2");
	$result = mysql_query("UPDATE cms_settings SET value='$siteowner' WHERE setting_id=3");
	$result = mysql_query("UPDATE cms_settings SET value='$rootemail' WHERE setting_id=4");
	$result = mysql_query("UPDATE cms_settings SET value='$rootpass' WHERE setting_id=5");
	$result = mysql_query("UPDATE cms_settings SET value='$rootadress' WHERE setting_id=7");
	$result = mysql_query("UPDATE cms_settings SET value='$rootzip' WHERE setting_id=8");
	$result = mysql_query("UPDATE cms_settings SET value='$rootcity' WHERE setting_id=9");
	$result = mysql_query("UPDATE cms_settings SET value='$rootphone' WHERE setting_id=10");
	$result = mysql_query("UPDATE cms_settings SET value='$rootpaypal ' WHERE setting_id=11");
	$result = mysql_query("UPDATE cms_settings SET value='$rootbankwire' WHERE setting_id=12");
	$result = mysql_query("UPDATE cms_settings SET value='$rootcountry' WHERE setting_id=13"); 

// ....
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Code: Select all

<?php

$default = array(
   'Sitename' => 'Defeault sitename',
   'Siteurl' => 'http://localhost',
   'Siteemail' => 'jcart@thecoolest.com'
);

$configs = array_intersect_key($_POST, $default);

foreach ($configs as $type => $value) {
   $sql = sprintf(
      'UPDATE `configuration` SET `value` = \'%d\' WHERE `setting` = \'%s\'', 
         mysql_real_escape_string((empty($value) ? $default[$key] : $value)),
         mysql_real_escape_string($type)
   );
   
   mysql_query($sql) or die(mysql_error());
}
I typically do something like this when dealing with configuration variables. Enjoy.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Since you've discovered the joy of using sprintf: http://www.timvw.be/elegantly-generate-sql-queries/
Post Reply