Page 1 of 2

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

Posted: Sat Oct 28, 2006 8:04 pm
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?

Posted: Sat Oct 28, 2006 8:06 pm
by volka
Add a WHERE clause to your sql statement that only selects some records.
see http://www.w3schools.com/sql/sql_where.asp

Posted: Sat Oct 28, 2006 8:08 pm
by thiscatis
But how do you point your variables to look to the right row then?

Posted: Sat Oct 28, 2006 8:08 pm
by volka
I don't understand the question.

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

Posted: Sat Oct 28, 2006 8:11 pm
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

Posted: Sat Oct 28, 2006 8:14 pm
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?}

Posted: Sat Oct 28, 2006 8:17 pm
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.

Posted: Sat Oct 28, 2006 8:20 pm
by thiscatis
recieved the aha-feeling now ;)

Posted: Sat Oct 28, 2006 8:28 pm
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

Posted: Sat Oct 28, 2006 8:39 pm
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

Posted: Sat Oct 28, 2006 9:43 pm
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

Posted: Sat Oct 28, 2006 10:29 pm
by John Cartwright
the print_r($config) will show you the keys in your array..

access them by $config['key_name_here']

Posted: Sat Oct 28, 2006 11:46 pm
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"); 

// ....

Posted: Sun Oct 29, 2006 12:18 am
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.

Posted: Sun Oct 29, 2006 3:10 am
by timvw
Since you've discovered the joy of using sprintf: http://www.timvw.be/elegantly-generate-sql-queries/