Logic Conundrum. [SOLVED]

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

Post Reply
User avatar
spedula
Forum Commoner
Posts: 81
Joined: Mon Mar 29, 2010 5:24 pm

Logic Conundrum. [SOLVED]

Post by spedula »

Okay. Where to begin :?:

I've thought/coded myself into a bit of a pickle here. Lets start with some pre-text.

I'm building a custom CMS that will enable users to create their own store fronts using my application. All data will be hosted on my server. I've coded the template engine and am working on the 'site settings' part of my application, basically where users control what goes where (every aspect of template is modular) and CSS styles.

I had originally coded it to only account for one CSS file for all pages, I have since re-thought that and made it two. The template engine opens up two CSS files with [%TAGS%] as value replacement place holders. One is style file is for global styles, like site colors, logo, etc... and the other is for local, per page, styles like column widths and spacings for that specific page. The site settings page should be composed of two parts then, global and local settings.

The database is normalized, so one table holds ALL of the placeholder tags with their respective id's, as such:

id, name
1, logo
2, body_color
3, left_width

and then another table holds all the value for said tags as such:

storeid, pageid, keyid, elementValue
1, 1, 1, image.png
1, 1, 2, #CCCCCC
1, 1, 3, 400px

keyid is the relative id for the placeholder tag from table 1.

The code that I originally wrote, which reads all the tags from table 1 and then vales from table 2 is this:

Code: Select all

//top bit
$query = mysql_query("SELECT id, name FROM keyNames") or die ("2 ".mysql_error());
$ki = 0;
$keyNames[] = '';
$keyid[] = '';
while($rowdata = mysql_fetch_array($query)) {
	$keyid[$ki] = $rowdata[id];
	$keyNames[$ki] = $rowdata[name];
	$ki++;
}

// bottom bit
$keyCount = count($keyNames);
for ($i = 0; $i < $keyCount; $i++) {
	$query = mysql_query("SELECT elementValue FROM sitesettings WHERE storeid='".$storeid."' AND keyid='".$keyid[$i]."' AND pageid='".$pageid."'") or die ("3 ".mysql_error());
	$rowdata = mysql_fetch_array($query);
	$values[$i] = $rowdata[elementValue];
}
Then I use some JS to display the form fields nicely on the site settings page. The tricky part is keepings both the $keyNames array and $values array to have the same offsets, so that they match with a key/value pair. I know this is probably a horrible way to do this, but I don't know any better way. So some help here would be greatly appreciated.

If I continue on this tricky offset matching path, then the issue is that I need to separate the global style tags away from the local style tags. I'm not sure where the best place to do that would be, logically. I tried to code it into the top bit (query from keyNames) with no success, it just messes up my offset matching. And coding it into the bottom bit will most likely give me the same type of garbage.

I'm at my wit's end here. :banghead: I'm still very new to PHP, only about 3.5 months of real coding experience, so my logical/comp-sci thinking isn't as refined as it needs to be for an issue this complex. I hope what I wrote makes sense, it took me way to long to get these thoughts into words, :crazy:

Thank you in advance.
Last edited by spedula on Wed Jan 12, 2011 8:49 pm, edited 1 time in total.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Logic Conundrum.

Post by mikosiko »

I see that you have another post with the same issue more or less, therefore better to stick just with one post...

The code that you posted is only partial code and doesn't give exactly the idea of how you are using the data... therefore you can get no exact/correct answers... either way... just looking the code that you posted... why are you doing 2 selects (one in a loop 8O ) if you can associate both with just one using a JOIN?... and in top of that you are storing the values in 2 arrays... why don't use only one?... is any reason in your design to code in the way that you are doing? ...

I probably will do something like this : (simplified -untested)

Code: Select all

$sql = "SELECT a.id, a.name, b.elementValue
                 FROM keyNames a JOIN sitesettings b ON a.keyid = b.keyid
               WHERE b.storeid = '" . $storeid . "' AND b.pageid = '" . $pageid . "'";

$rowdata = mysql_query($sql) or die ("Query Error: ".mysql_error();

while($row = mysql_fetch_assoc($rowdata)) {
        $arrayvalues[] = $row;
}
....
and now I can use $arrayvalues[] in any way I want
User avatar
spedula
Forum Commoner
Posts: 81
Joined: Mon Mar 29, 2010 5:24 pm

Re: Logic Conundrum.

Post by spedula »

If you're referring to the mySQL/PHP performance thread, that one is about the actual performance of mySQL depending on how many rows there are in a tbl.

As for your JOIN idea. I've never heard of this before, but it looks like you just set me on the right track and definitely broke me out of my coding rut.

I will look into this. Thank you so much!!! :D
User avatar
spedula
Forum Commoner
Posts: 81
Joined: Mon Mar 29, 2010 5:24 pm

Re: Logic Conundrum.

Post by spedula »

I tried using the example code you gave me, modified it several times, and can't produce any results no matter what I do.

I did a var_dump on $rowdata and got: resource(3) of type (mysql result)
What does that mean?

Also, var_dump on $row, inside the while loop, and nothing returned.

Edit: Disregard this post.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Logic Conundrum.

Post by pickle »

I take offense to you using the term "pickle" to indicate an unsolvable problem ;)

Anyway - I agree with ~mikosiko - use a JOIN. Remember that a simple 'WHERE' clause is considered a join.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
spedula
Forum Commoner
Posts: 81
Joined: Mon Mar 29, 2010 5:24 pm

Re: Logic Conundrum.

Post by spedula »

In case anyone runs into a similar issue in the future, I've solved this issue with the help of everyone here and also one of my programmer friends.

Code: Select all

$sql = "SELECT a.id, a.name, b.elementValue
		FROM keyNames a 
		LEFT JOIN sitesettings b ON (b.keyid = a.id AND b.storeid = '" . $storeid . "' AND b.pageid IN (0, " . $pageid . ") )";
That will return a.id and a.name even if no data is present in sitesettings.
Post Reply