Page 1 of 1

print xml nodes from iterate and sub iterate query

Posted: Mon Aug 21, 2006 8:54 pm
by glennn.php
i'm trying to build nodes in an xml file from a database of persons and mp3's they've submitted. i need to query a list of $names and query a sublist of $mp3s for each $name - i can get an array of either one, but i don't know how to get a subarray for each.

i don't even know how to ask the question!

can someone offer some kind assistance?

thanks much

glenn

Posted: Mon Aug 21, 2006 8:57 pm
by feyd
Can you post the table structures of the tables involved and how the fields interact? It's best if the table structures are SQL "CREATE TABLE" syntax as that gives us the finer details needed.

Posted: Mon Aug 21, 2006 9:23 pm
by glennn.php
right - i just realized my problem - i have the proper tables CREATED, but not all the mp3's are in them. (long story ) - so let's suffice it to say

db.stars contains "Bill" who has his own table, db.sj which would have three songs in it...

Code: Select all

CREATE TABLE `stars` (
  `customerid` bigint(32) NOT NULL auto_increment,
  `clicks` varchar(255) NOT NULL default '',
  `picId` varchar(255) NOT NULL default '',
  `url` varchar(255) NOT NULL default '',
  `firstname` varchar(40) default NULL,
  `lastname` varchar(50) default NULL,
  `password` varchar(20) default NULL,
  `aka` varchar(255) NOT NULL default '',
  `email` varchar(255) default NULL,
  `website` varchar(255) default NULL,
  `city` varchar(50) default NULL,
  `state` varchar(50) default NULL,
  `talent` varchar(255) default NULL,
  `biography` text,
  `av` varchar(5) NOT NULL default '',
  `approved` varchar(11) NOT NULL default '0',
  PRIMARY KEY  (`customerid`)
) TYPE=MyISAM PACK_KEYS=1 AUTO_INCREMENT=417 ;



CREATE TABLE stevenjiles (
  mp3 varchar(255) NOT NULL default '',
  ip varchar(255) NOT NULL default '',
  time varchar(255) NOT NULL default '',
  PRIMARY KEY  (ip)
) TYPE=MyISAM;

this is all i need to get printed:

Code: Select all

print "
<node>
	<node label=\"steven jiles\">
		<node label=\"So Happy\" src=\"audio/anita4.mp3\" artist=\"Asbury Park Angel\" album=\"Asbury Angel\" track=\"1\" cover=\"../stars/asburyangel.jpg\" />
		<node label=\"Devoted\" src=\"audio/anita3.mp3\" artist=\"Asbury Park Angel\" album=\"Asbury Angel\" track=\"2\" cover=\"../stars/asburyangel.jpg\" />
		</node>
	</node>
</node>";
thanks for your help

Posted: Mon Aug 21, 2006 9:33 pm
by feyd
I don't understand how the posted table structures translates to the code you wish to have.

An aside: disabling bbcode makes the highlighting not work, and

Code: Select all

..
is used for SQL queries.

Posted: Mon Aug 21, 2006 9:33 pm
by glennn.php
this is what i've been working with for other related queries:

Code: Select all

$target_qid = mysql_query("SELECT CONCAT(firstname , ' ' ,lastname) AS FullName, picId, url, FROM stars WHERE url like '%mp3' ORDER BY lastname");

// $target_count = 0;
$string.= "<?xml version=\"1.0\"\x3F>\n";

while($target_row = mysql_fetch_array($target_qid))
	{
	$target = trim($target_row[2]);
	$target = ereg_replace(" ", "%20", $target);
	$pic = trim($target_row[1]);
	$pic = ereg_replace(" ", "%20", $pic);
	$name = ucwords($target_row[0]);
	$bio = $target_row[4];

$string.= "<node>\n";
$string.= "<node label=\"".$Fullname."\">\n";

//...............

Posted: Mon Aug 21, 2006 9:38 pm
by glennn.php
feyd wrote:I don't understand how the posted table structures translates to the code you wish to have.

An aside: disabling bbcode makes the highlighting not work, and

Code: Select all

..
is used for SQL queries.
sorry -

i don't either. that's why i'm asking the smart people. i just know what i need to have done so i'm not manually inputting mp3s into xml files all day. right now we have about 300 people and my partner is about to start paying for advertising in China. i HAVE to automate this somehow...

Posted: Mon Aug 21, 2006 9:44 pm
by feyd
Because the people have separate tables (who came up with that idea?) you'll need to loop over the names returned from your query above. Hopefully they're algorithmicly named in nature so it's easier to match up who goes to what.

If the mp3 tables were a single table it would be a simple, single, query.

Posted: Mon Aug 21, 2006 9:49 pm
by glennn.php
no - that bridge hasn't been crossed yet (the individual tables were there for another reason - i thought i'd need to use them to store multiple mp3 records - how would i put three mp3's in Steven Jiles' row and later extract them?

i was just looing at this, which looks like what i need:

Code: Select all

 
$sql = "SELECT id as userid, fullname, userstatus 
       FROM  sometable
       WHERE  userstatus = 1";

$result = mysql_query($sql);

if (!$result) {
   echo "Could not successfully run query ($sql) from DB: " . mysql_error();
   exit;
}

if (mysql_num_rows($result) == 0) {
   echo "No rows found, nothing to print so am exiting";
   exit;
}

// While a row of data exists, put that row in $row as an associative array
// Note: If you're expecting just one row, no need to use a loop
// Note: If you put extract($row); inside the following loop, you'll
//      then create $userid, $fullname, and $userstatus
while ($row = mysql_fetch_assoc($result)) {
   echo $row["userid"];
   echo $row["fullname"];
   echo $row["userstatus"];
}

mysql_free_result($result);


Posted: Mon Aug 21, 2006 10:01 pm
by feyd
glennn.php wrote:no - that bridge hasn't been crossed yet (the individual tables were there for another reason - i thought i'd need to use them to store multiple mp3 records - how would i put three mp3's in Steven Jiles' row and later extract them?
I would generally break it down like this in tables: Artist, Album, and Track. Depending on how flexible you build it, Track could point to one or more artists. Since tracks can appear on multiple albums and albums generally have multiple tracks, thus a many-to-many relationship I would have a linking table that has a column for album identifier and another column for track identifier. This linking table would probably hold track number as well.

It could be broken down even further, but that's the basics.

---

I'm not sure I can comment on the code posted however.

Posted: Mon Aug 21, 2006 10:06 pm
by glennn.php
feyd wrote: ---

I'm not sure I can comment on the code posted however.
came from some dumb site called php.net. some kind of tutorial or something, they say...


;o)