[SOLVED] MYSQL_ASSOC vs MYSQL_NUM

[iarp]

I had this snippet of coding below:

$query = "SELECT config_value FROM " . TBL_CONFIG;
$result = @mysql_query($query); //run the query
$num = mysql_num_rows($result);

if ($num > 0) { //vaild user id, show the form.

//get the user's information.
$row = mysql_fetch_array($result, MYSQL_ASSOC);

 

Can i not just call $row['site_name'] rather then $row[0] and have to change MYSQL_ASSOC back to MYSQL_NUM ? I take it ASSOC allows you to use a fields name?

 

In my database i have 2 columns called config_name and config_value

 

and only 2 rows populated

 

config_name    config_value

  site_name        iarp

  location          /test/

[corbin]

ASSOC stands for associative, and NUM stands for numerical.

 

That's referering to the indexes of the array.  For example, in an associative array, the keys and values are "associated."  In this context, that means that it would be config_value => value instead of 0 => value.

 

That's a terrible explanation technical-wise, but as far as practicality goes, it's decent enough.... x.x.

[haku]

use this:

 

$row = mysql_fetch_array($result);

 

And you can refer to the results either by index number or by name.

[iarp]

Maybe a better question would be, how could i select the value from the config_value field where the config_name equals whatever $row is set to?

 

Like if i were to make a page for someone to edit the 2 rows i have listed above on the same page, what query should i use... gettin all confused now i've tired many different things at least to my best ability.

[haku]

It doesn't really make sense - are config_value and config_name both the same type of value? And if they are, then your first query is going to pull every config_value out of the database, although you only pass the first value to $row. Is the first value the only one that matters?

[iarp]

This is the table:

CREATE TABLE `GAMER_config` (
  `config_id` int(10) unsigned NOT NULL auto_increment,
  `config_name` varchar(40) NOT NULL default '',
  `config_value` longtext NOT NULL,
  `active` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`config_id`)
) TYPE=MyISAM AUTO_INCREMENT=5 ;

-- 
-- Dumping data for table `GAMER_config`
-- 

INSERT INTO `GAMER_config` VALUES (1, 'site_name', 'Gamer Strategy', 1);
INSERT INTO `GAMER_config` VALUES (2, 'copyright', 'Gamer Strategy | Ian R-P', 1);
INSERT INTO `GAMER_config` VALUES (3, 'site_location', '/test/', 1);

I store some of the values in the database rather then a hardcoded included page because i find it's easier to edit the database values.

[trq]

Maybe a better question would be, how could i select the value from the config_value field where the config_name equals whatever $row is set to?

 

SELECT config_value FROM " . TBL_CONFIG . WHERE config_name = 'site_name';

[iarp]

That only selects the one value for site_name, unless i'm stuck having to do a query for each field.

[trq]

<?php

$query = "SELECT config_name,config_value FROM " . TBL_CONFIG;
if ($result = mysql_query($query)) {
  if (mysql_num_rows($result)) {
    while ($row = mysql_fetch_assoc($result)) {
      $config[$row['config_name']] = $row['config_value'];
    }
  }
}

// You now have an associative array containing your config information.
// You can print a single value. eg;
echo $config['site_name'];

// or loop through them all.
foreach ($config as $k => $v) {
  echo $k . ' = ' . $v . '<br />';
}

?>

[iarp]

Thanks, that worked very well..

 

My last thing i'm having a problem with, i have a feeling it'll require some type of loop

 

$query = "UPDATE " . TBL_CONFIG . " SET config_value='$sn' WHERE config_name='$sn1'"; 

 

works only for site_name, can't figure out how to change it for all values submitted(which is basically everything)

[trq]

Yes you will need some type of loop. You might want to post some more code.

[iarp]

The way i have it going on right now:

<?php
$page_title = 'Edit User';

require ('../includes/header.php');

if (isset($_POST['submitted'])) {
$errors = array();

if (empty($_POST['site_name'])) {
	$errors[] = 'You forgot to enter the sites name!';
} else {
	$sn = $_POST['site_name'];
	$sn1 = 'site_name';
}

if (empty($_POST['site_location'])) {
	$errors[] = 'The site is located where?';
} else {
	$sl = $_POST['site_location'];
}

if (empty($_POST['copyright'])) {
	$errors[] = 'You need the copyright';
} else {
	$cr = $_POST['copyright'];
}

if (empty($errors)) {
	$query = "UPDATE " . TBL_CONFIG . " SET config_value='$sn' WHERE config_name='$sn1'";
	$result = mysql_query($query); //run the query
	if (mysql_affected_rows() == 1) { // if it ran ok
		//print a message
		messages(4);
	} else {// if it did not run ok
		echo '<h1> System error</h1> You didn\'t make any changes!';
	}
} else { //report the errors
	echo '<h1> Error</h1> <p class="error"> The following errors occured:<br />';
	foreach ($errors as $msg) { //print each error
		echo " - $msg<br />\n";
	}
	echo '</p><p>Plese try again.</p><p><br /></p>';
}
}

$query = "SELECT config_name,config_value FROM " . TBL_CONFIG;
if ($result = mysql_query($query)) {
  if (mysql_num_rows($result)) {
    while ($row = mysql_fetch_assoc($result)) {
      $config[$row['config_name']] = $row['config_value'];
    }
  }
}

echo '<h3> Site Config </h3>
<form action="config.php" method="post">
	<p>Site Name: <input type="text" name="site_name" value="' . $config['site_name'] . '" /></p>
	<p>Site Location: <input type="text" name="site_location" value="' . $config['site_location'] . '" /></p>
	<p> Copyright: <input type="text" name="copyright" value="' . $config['copyright'] . '" /></p>
	<input type="submit" name="submit" value="Submit!" />
	<input type="hidden" name="submitted" value="TRUE" />
</form>';

require('../includes/footer.php');
?>

 

I've only got the one query because i'm unsure of how to right a loop to do the rest of them.

[trq]

This should give you the idea.

 

<?php

  foreach ($_POST as $k => $v) {
    if ($k != "submit" || $k != "submitted") {
      mysql_query("UPDATE " . TBL_CONFIG . " SET config_value='$v' WHERE config_name='$k'");
    }
  }

?>