[PHP/SQL] Data from multiple rows in one table
Moderator: General Moderators
[PHP/SQL] Data from multiple rows in one table
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?
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?
Add a WHERE clause to your sql statement that only selects some records.
see http://www.w3schools.com/sql/sql_where.asp
see http://www.w3schools.com/sql/sql_where.asp
Re: [PHP/SQL] Data from multiple rows in one table
Something like...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?
Code: Select all
SELECT * FROM table WHERE setting_id IN( 1, 3 );printf
if I have:
question1
edit// so 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?}hmm still no luck..
i now have:
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
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 )) {
//?
}What's the php code there to site e.g
$siteowner = the value of row1
$zipcode = the value of row3
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
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);- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Thanks!
Working now;)
Another question, don't want to open a new topic for this one
is there a better way to achieve this:
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");
// ....- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
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());
}Since you've discovered the joy of using sprintf: http://www.timvw.be/elegantly-generate-sql-queries/