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