mysql_fetch_a-mind-boggler - can't get the output i want

[ATS16805]

please take a look at my code. you should be able to determine my desired output by glancing at my "test string" outputs i've put in my HTML Block string variable for printing to screen to test what's happening. i've done a ton of mysql_fetch_array's in this app, but i can't do that here, and i just can't see WHY this isn't giving me the data i'm trying to output (which is a "VARCHAR" field from my table).

$display_block .= "<p><strong>Concert Scheduled for ".$show_date.":</strong><br>
		<ul>";


	$artist1_nameqry = "select artist_name from artist_info where artist_id = $artists_res1";
	$artist2_nameqry = "select artist_name from artist_info where artist_id = $artists_res2";
	$artist3_nameqry = "select artist_name from artist_info where artist_id = $artists_res3";
	$artist4_nameqry = "select artist_name from artist_info where artist_id = $artists_res4";
	$artist5_nameqry = "select artist_name from artist_info where artist_id = $artists_res5";
	$artist6_nameqry = "select artist_name from artist_info where artist_id = $artists_res6";
	$nameres1 = mysql_query($artist1_nameqry) or die(mysql_error());
	$nameres2 = mysql_query($artist2_nameqry);
	$nameres3 = mysql_query($artist3_nameqry);
	$nameres4 = mysql_query($artist4_nameqry);
	$nameres5 = mysql_query($artist5_nameqry);
	$nameres6 = mysql_query($artist6_nameqry);



	// HERE'S THE ERROR:
	// THESE VARIABLES HOLD NOTHING, and therefor output nothing! 
	// WHY?

	$artist1name = mysql_fetch_array($nameres1['artist_name']);
	$artist2name = mysql_fetch_array($nameres2['artist_name']);		
	$artist3name = mysql_fetch_assoc($nameres3['artist_name']);
	$artist4name = mysql_fetch_row($nameres4['artist_name']);
	$artist5name = mysql_data_seek($nameres5['artist_name']);
	$artist6name = mysql_fetch_assoc($nameres6['artist_name']);

	$display_block .= "<p>Artist1_nameqry is $artist1_nameqry</p>";
	$display_block .= "<p>Artist2_nameqry is $artist2_nameqry</p>";
	$display_block .= "<p>Artist3name is $artist3name</p>";
	$display_block .= "<p>Nameres1 is $nameres1, the info needed to pull the 
					   info into the name 			  variables which use 
					   Nameres1['artist_name']!</p>";
	$display_block .= "<p>Just to test the theory, Nameres2 is $nameres2</p>";
	$display_block .= "<p>Artist1name is $artist1name now that i changed the array info</p>";
	$display_block .= "<p>Artist2name is $artist2name just for shits and beans</p>";



	$display_block .= 	"<div id=\"secondloop\">
						<li>".$artist1name."</li>
						<li>".$artist2name."</li>
						<li>".$artist3name."</li>
						<li>".$artist4name."</li>
						<li>".$artist5name."</li>
						<li>".$artist6name."</li>
						</ul></div>";
	$display_block .= "<p>Artist One ID is $artists_res1</p>";
	$display_block .= "<p>Artist Two ID is $artists_res2</p>";
	$display_block .= "<p>Artist Three ID is $artists_res3</p>";
	$display_block .= "<p>Artist Four ID is $artists_res4</p>";
	$display_block .= "<p>Artist Five ID is $artists_res5</p>";
	$display_block .= "<p>Artist Six ID is $artists_res6</p></div>";

}

is there somethign else i should do to test this, or did you find an error in my code?

[thorpe]

they should be...

$artist1name = mysql_fetch_array($nameres1); 
$artist2name = mysql_fetch_array($nameres2);         

$artist3name = mysql_fetch_assoc($nameres3); 
$artist4name = mysql_fetch_row($nameres4); 
$artist5name = mysql_data_seek($nameres5); 
$artist6name = mysql_fetch_assoc($nameres6);

mysql_query returns a result set, not an array. but really, you should rethink your whole approuch here. why so many queries?

[ATS16805]

hey, thanks!! i didn't study and implement your code yet, but i wanted to ask also...

PS. i tried using mysql_fetch_array by using something like this, but it doesn't seem to work. can i modify this in some manner so that i mustn't repeat so much code? surely there's a better way than what i've done.

while ($myStringArray = mysql_fetch_array($MySQL_Query_Res[$z]) {

$z = 0; /* if i can turn the value of $z into a string, maybe this would work? */

$zAsString = $z; 

/* wishing there were some PHP5 function to make this work here! 
I'm TOTALLY shooting in the dark here - 
- realizing this doesn't exist according to php.net
but, as in:

$zAsString = int_as_string($z) 

then the rest of the loop might work as follows to complete the loop*/

$String.$zAsString = $MyStringArray[$z];
$z++;
}

the idea being to create strings dynamically w/out typing their names

[ATS16805]

oops. sorry 'bout the French in the first post!

oh, and i see that i forgot to change those all back... wait, are they supposed to be mysql_fetch_assoc(_), correct? or should it be mysql_fetch_array()?

[ATS16805]

wait! i'm using your code, thorpe (some mysql_fetch_assoc() and some mysql_fetch_array() just to test) and all are outputting just

Array

that's why i thought i needed the [array_key] in there! hmph! HELP! i'm falling....
:queasy:

[thorpe]

ive no idea what your trying to do really. your code makes little sense. with a better query you should be able to get all the data you want into one result set. then loop through that.

what is your db structure, and what data do you want from it? all your queries look almost identical. tell me, what it the difference between these two?

$artist1_nameqry = "select artist_name from artist_info where artist_id = $artists_res1";
$artist2_nameqry = "select artist_name from artist_info where artist_id = $artists_res2";

what does $artists_res1 and $artists_res2 hold?

as for the value Array being printed. thats how array work. you need to loop through there elements to get any result.

[ATS16805]

indeed, i do find it difficult to know when to be so redundant-- as my cries for help here for so many weeks have all been in regard to this-- so i'd hate for anyone to think "oh, god! what is with this guy and that stupid db jpg!". cause i almost always include it! 😉
and the one time when i didnt... heehee
here's the schema-- almost --as i have added a table titled "lineup" which holds (what once was, but is no longer in the 2nd - 4th field of "concerts" in this illustration) the Artist_id1 - Artist_id6, so as to normalize that out of the "concerts" table, as per a suggestion from someone at mysql.com forum. i'm not 100% convinced it was the right decision, but i think it was.

i'll try to come up w/ the rest of my answer here in a min...
:bemused:

[thorpe]

well you've got me beat. i dont even see a artist_inf table in that very colorfull schema of yours.

im going to have a quick guess, and just show you an example. i take it you want the names of artists with id's 1 - 6.

<?php
  $query = "SELECT artist_name FROM artist_info WHERE artist_id BETWEEN 0 AND 7;";
  $result = mysql_query($query);
  if ($result) {
    while ($row = mysql_fetch_assoc($result)) {
      echo $row['artist_name']."<br />";
    }
  } else {
    echo "query failed";
  }

[ATS16805]

thorpe wrote:

very colorfull schema

hmm.. is that "colorfull", as in "..because he had quite a colorful personality..", or as in people who are not color-blind are able to see a colorful contrasting spectrum on an RGB display? heehee

was there supposed to be an artist_inf table somewhere? not sure what you meant, or why it is confusing or getting you "beat" as you say.

thanks for the code! can you provide a quick explanation of what it's doing, please? just trying to get a hold of your reasoning behind that choice.

EDIT:
oh! the output... it's rather complicated, i guess-- imagine a ton of artists, each w/ a corresponding ID. so, we might have the beatles #9, led zeppelin#4, j.s.bach#5, the skiffle kings#694, etc
the lineup table has 6 fields for artists, so that i can indicate a "Lineup" of 6 artists for any given concert date, the concert date having it's own id#.
let's say that concert id#357 has lineup of #268. i need to echo those artists which make up lineup #268, which might be #9, #4, #5, #694, or, maybe only #9 in fact. hmm... so, i guess if your code will just loop the row, then that's my best solution.

my problem w/ the completion of this app is that i added that lineup table so late in the game here. it was nearly finished, and now i have to re-write a ton of code, and figure out how to get stuff to work right-- like the simultaneous logging of ID for concert and Lineup when those data are entered.

blah, blah. hope that helps it all make sense. i forgot i never explained that part. i guess it wasn't relevant when i first posted.... but since i asked about a more efficient way of coding this section...

[ATS16805]

thorpe wrote:

as for the value Array being printed. thats how array work. you need to loop through there elements to get any result.

hmph! i missed this.

i thought it was like this:

$stickman = array("skinny", "thin", "black", "white", "line-drawing", "worthless");

and then i could do this:

echo $stickman[3];
// will output "white"

whereas in a multidimensional array, i can do...

echo $stickman['type-of-cartoon'];
//output would be "line-drawing"

hmm... i'm missing something there, huh

[ATS16805]

bump cause i'm guessing it didn't make sense before, and it might now

and, no, your code (at least not in its current form) doesn't acheive what i want

[ATS16805]

MY LAST, BEST EFFORT TO ILLUSTRATE THE PURPOSE OF CODE IN QUESTION:

the reason for code in post #1 here is to grab the names of those artists which correspond to those ID's in the Lineup Table.

• the artist names come from table: "artist_info",

• artist id's from table: "lineup" (not shown in schema jpg -- because added after app was "built" for refining purposes)

• concert id from table: "concerts" (which provides artist ids indirectly via included lineup table row id), ALSO which holds the data which builds the <select><option> input for the ultimate $_POST data which starts the process

the UNSEEN CRITICAL component here, the concertID which was selected via <option><select> drop down via selection of the concert date data via $_POST

so the relation is like this for the Query:
concert date is $_POST data
concert date = concert id = lineup id = artist id's = artists names = output

[Weedpacket]

Let me take a stab at this (I'm going to take a punt without looking at the schema first). So the lineup table is to join concerts to artists (so there can be varying numbers of artists per concert). The concert and artist tables each have a (unique) ID field. The concert table has a date field and the artist table has a name field.

Given a concert date, you want a list of the artists performing at the concert (or at the concerts?) on that date.

SELECT
	artist.artist_name
FROM
	artist, lineup, concert
WHERE
	concert.date = [i]$date[/i]
AND	concert.id = lineup.concert_id
AND	lineup.artist_id = artist.id

Let me see if I can picture it (this isn't how the DBMS does it, but the effect is the same)

• Make one whacking great huge table with fields called concert.id, concert.date, lineup.concert_id, lineup.artist_id, artist.id, artist.artist_name. There is one row for every possible selection from the three tables, so if there were 3 rows in the concert table, 12 rows in the artist table, and 7 rows in the lineups table, there'd be 3*127=252 rows in the combined table.
  

• Throw out all the rows where lineup.artist_id doesn't match artist.id.
  

• Throw out all the rows where lineup.concert_id doesn't match concert.id
  

• Throw out all the rows with dates that don't match $date
  

• Keep only the artist_name field, and return that as the result
  

[ATS16805]

Weedpacket! Congratulations! you hit the nail on the head. sorry to Thorpe for not providing a better initial explanation of things. i have never used the table.field syntax in my SELECT statements yet, but this sounds like a good time to start! thanks! i've learned something!!!

WP, i'm not sure however what you're saying here in your second list. i mean, you are illustrating what's going on-- but... why? if it's to confirm that you are inside of my line of thought, then yes, this is what's going on. if there's another purpose, then i've missed it. could you explain please? is this to show inefficiency or something?

i do NOT already have this "whacking great huge table", but why do i need it-- isn't that what MySQL is doing for me w/ the selects and my schema, etc?

is this something that i can in some way generate dynamically? hmm.... i'm sure there's something here that i'm just not getting yet.

Weedpacket wrote:

• Make one whacking great huge table with fields called concert.id, concert.date, lineup.concert_id, lineup.artist_id, artist.id, artist.artist_name. There is one row for every possible selection from the three tables, so if there were 3 rows in the concert table, 12 rows in the artist table, and 7 rows in the lineups table, there'd be 3*127=252 rows in the combined table.
  

• Throw out all the rows where lineup.artist_id doesn't match artist.id.
  

• Throw out all the rows where lineup.concert_id doesn't match concert.id
  

• Throw out all the rows with dates that don't match $date
  

• Keep only the artist_name field, and return that as the result
  

EDIT: don't know if it matters to anyone or not, but i edited my most recent post before this to clarify what's going on to arrive at this select query in question.

[Weedpacket]

Yes; I was just writing that as I checked the query I wrote to see if what it did was what it was supposed to.

[ATS16805]

Weedpacket wrote:

Yes; I was just writing that as I checked the query I wrote to see if what it did was what it was supposed to.

ah! that's a cool "rule of thumb"!! (for lack of a better term)

i'll have to try it myself someday. i think i can see the thought process required to come up w/ that-- this probably sounds silly, but you wouldn't happen to have a URL handy, linked to any tutorials which discuss this type of "reasoning", do you? or is this just "your own" little formula which you never really saw used elsewhere by others?

[Weedpacket]

I did come across it in a textbook on SQL - 'fraid I don't remember the title, author, publisher or even what exactly it said, sorry. It had a black cover, if that's any help 😃

[ATS16805]

hmmm... SQL books w/ black covers... i'll have to make a mental note.

in any event, this is something that i'll get a great deal of mileage out of. think of me if you ever recall the resource whence you first saw it-- or even any other online sources of "good rules of thumb" for this type of thing. thanks again, WP.

[Weedpacket]

Just read through this thread again. On the subject of $zAsString....
$zAsString = [man]strval/man; one could also do some type juggling ($zAsString = ''.$z😉 or casting ($zAsString=(string)$z😉.

[ATS16805]

i just did the same -- read through this thread-- i have no idea what i ingested that day, but i sure sound like a crazy person. whoa!
i looked at $zAsString and thought "what, is WP toying w/ me because of this confused thread of mine?", and then went on to realize that it was in fact my OWN writing. i must have been VERY engrossed in thought about this whole thing that day-- trying to find workarounds and such.

anyway-- regarding the $zAsString .... are those supposed to be smiley faces? i'm getting kinda confused by that (and, i'm obvioulsy already quite confused, so...). would you mind just a bit more detail on what you're telling me here, WP, please?

Edit:
okay, i see now that $string [concatenate] $zAsString -- it was supposed to dynamically generate something as such:

$someString1 , $someString2 , $someString3

and so on. i see what i was trying to do now. but, i'm still unsure of your suggestion just above here, WP.