Jump to content

join and loop issues

dropfaith

By dropfaith
in PHP Coding Help

 Share

Recommended Posts

dropfaith Advanced Member

dropfaith

Posted
Posted 
<?php
		
			// Check connection
			if($link === false){
   			 die("ERROR: Could not connect. " . mysqli_connect_error());
					}
   			echo "<div class='innerleft'>";           
				// Attempt select query execution
				$id = $_GET['spotlight'];
				$sql = "SELECT champions.* , synergy.* ". "FROM champions, synergy ". "WHERE champions.id = $id AND synergy.idd = $id";
				if($result = mysqli_query($link, $sql)){
    				if(mysqli_num_rows($result) > 0){      
				while($row = mysqli_fetch_array($result)){
				echo "<span>" . $row['Name'] . "";
    				echo "</span>";
				echo "<img src=" . $row['image'] . ">";
				echo "<span>Statistics</span>";	
				echo "<ul class='stats'>";
			?>
			
			
			
			
			<div id="stwo" style="display:none;">
						<h3>Out Going Synergys</h3>
						<table>
							<?php
    							echo "<tr>";
    							echo "<td><img class='small' src=" . $row['image'] . ">";
    							echo "</td><td>" . $row['Synergy'] . "";
    							echo "</td></tr>";
							?>
						</table>
						<h3>InComing Synergys</h3>
						<table>
				
						</table>
					</div>

Im likely going to feel stupid once this is answered but im drawing a blank on how to manage this  

 

the top part needs not be displayed ones its table.champions

the part in the hidden div has multiple rows that need to loop  thru (table.synergy)

 

right now its looping the top part but not the second part and if i limit 1 it prevents the second part from a loop as well.

 

im basically teaching myself as i go here

 

 

Link to comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

Sorry, but I am having great difficulty trying to understand what you are trying to do here. Some extra info should clarify (hopefully)

  • What are your table structures?
  • Can you provide sample data?
  • Can you show us how you expect that sample data to appear in your output?

Meanwhile some comments on your query

  • Do not use SELECT *. Specify just those columns that actually need
  • Do not put values directly into the query, use prepared queries.
  • Use explicit join syntax IE. ... FROM champions c  JOIN synergy s  ON c.id = s.idd WHERE c.id = ?
Link to comment
Share on other sites
dropfaith Advanced Member

dropfaith

Posted
  • Author
Posted

http://roguebrother.x10host.com/spotlight.php?spotlight=49

Click Synergy on that in the content pane

and it displays more context  

i pretty much need all the stuff so * seemed most efficient?

 

CREATE TABLE IF NOT EXISTS `synergy` (
  `idd` int(125) NOT NULL,
  `name` varchar(500) NOT NULL,
  `Champ` varchar(50) NOT NULL,
  `sid` int(124) NOT NULL AUTO_INCREMENT,
  `Synergy` varchar(6000) NOT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;

 

 

CREATE TABLE IF NOT EXISTS `champions` (
  `Name` text NOT NULL,
  `image` text NOT NULL,
  `class` text NOT NULL,
  `id` int(125) NOT NULL AUTO_INCREMENT,
  `spotlight` varchar(125) NOT NULL,
  `4stats` text NOT NULL,
  `5stats` text NOT NULL,
  `6stats` text NOT NULL,
  `bio` text NOT NULL,
  `specials` text NOT NULL,
  `weaknesses` text NOT NULL,
  `strenghts` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=137 ;

 

theres probably a ton of things wrong here as i learn stuff 

 

 

 

 

Link to comment
Share on other sites
ginerjm Prolific Member

ginerjm

Posted
Posted

Posting links to "stuff" will not be acceptable to most readers of this (or any) forum.  Show Us The Code.  Not all of it - but enough of it that relates to the question.

As for your db design.  What are the fields 4stats, 5stats and 6stats supposed to be?  Different values of the same type of data?

Link to comment
Share on other sites
dropfaith Advanced Member

dropfaith

Posted
  • Author
Posted

the link is doing what i want it to do but im using  2 queries to do it and it loses the image which i really want from champion table   

 

only other thing i can think of is do a double query and merge the image into the other which should work but i figured 1 total query was better

Link to comment
Share on other sites
dropfaith Advanced Member

dropfaith

Posted
  • Author
Posted (edited) 
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'roguebro' with no password) */

// Check connection
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}
   echo "<div class='innerleft'>";           
// Attempt select query execution
$id = $_GET['spotlight'];
$sql = "SELECT * FROM champions WHERE id='$id'";
if($result = mysqli_query($link, $sql)){
    if(mysqli_num_rows($result) > 0){      
     $i = 0;
while($row = mysqli_fetch_array($result)){
    $i++;
    if ($i % 6 == 0) {
    }
  
  
  //dont loop this 
    echo "<span>" . $row['Name'] . "";
    echo "</span>";
	echo "<img src=" . $row['image'] . ">";
	
	echo "<span>Statistics</span>";	
	echo "<ul class='stats'>";
  
  
	?>

		<li><a href="javascript:PowerhouseShowHide('one=block','two=none','three=none')">Four Star</a></li>
		<li><a href="javascript:PowerhouseShowHide('one=none','two=block','three=none')">Five Star</a></li>
		<li><a href="javascript:PowerhouseShowHide('one=none','two=none','three=block')">Six Star</a></li>
	</ul>
	<div id="one" style="display:none;">
	<ul class="data">
		<?php	echo   $row['4stats']  ; ?>
	</ul>
	</div>
	<div id="two" style="display:none;">
	<ul class="data">
		<?php	echo   $row['5stats']  ; ?>
	</ul>
	</div>
	<div id="three" style="display:none;">
	<ul class="data">
	<?php	echo   $row['6stats']  ; ?>
	</ul>
	</div>
</div>
<div class='innerright'>
   		<ul class='abilities'>
			<li><a href="javascript:PowerhouseShowHide('sone=block','stwo=none','sthree=none','sfour=none','sfive=none')">Bio</a></li>
			<li><a href="javascript:PowerhouseShowHide('sone=none','stwo=block','sthree=none','sfour=none','sfive=none')">Synergy</a></li>
			<li><a href="javascript:PowerhouseShowHide('sone=none','stwo=none','sthree=block','sfour=none','sfive=none')">Specials</a></li>
			<li><a href="javascript:PowerhouseShowHide('sone=none','stwo=none','sthree=none','sfour=block','sfive=none')">Strenghts</a></li>
			<li><a href="javascript:PowerhouseShowHide('sone=none','stwo=none','sthree=none','sfour=none','sfive=block')">Weaknesses</a></li>
		</ul>
 
	<div class='innerrightcontent'>
	 	<div id="sone" style="display:block;">
			<?php	echo   $row['bio']  ; ?>
		</div>
		<div id="sthree" style="display:none;">
			<?php	echo   $row['specials']  ; ?>
		</div>
		 <div id="sfour" style="display:none;">
			<?php	echo   $row['weaknesses']  ; ?> 
		</div>
		<div id="sfive" style="display:none;">
			<?php	echo   $row['strenghts']  ; ?> 
		</div>
	</div>
</div>	

	<?php
}
         // Free result set
        mysqli_free_result($result);
    } else{
       /* echo "No records matching your query were found."; */
    }
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
} 
// Close connection
mysqli_close($link);


?>
		<div id="stwo" style="display:none;">
		<h3>Out Going Synergys</h3>
			<table>
				
					<?php
			
		
			// Check connection
			if($link1 === false){
    			die("ERROR: Could not connect. " . mysqli_connect_error());
			}           
			// Attempt select query execution
			$id = $_GET['spotlight'];
              
              
            // loop all this 
			$sql1 = "SELECT * FROM synergy WHERE idd='$id'";
			if($result1 = mysqli_query($link1, $sql1)){
    			if(mysqli_num_rows($result1) > 0){      
  
			while($row = mysqli_fetch_array($result1)){

    				echo "<tr>";
    				
    				echo "<td>" . $row['name'] . "";
    				
    				echo "</td><td>" . $row['Synergy'] . "";
    				echo "</td></tr>";
				}
        			 // Free result set
        			mysqli_free_result($result1);
   				 } else{
       				/* echo "No records matching your query were found."; */
    				}
				} else{
    				echo "ERROR: Could not able to execute $sql1. " . mysqli_error($link1);
				} 
				
				

			?>
			</table>

this does what i want  this from the link i posted...

 

but the join in theory was going to be better coded (learning as i go)  

 

so the table champions theres only 1 instance of Id (so no loop)

the table Synergy can have up to 8 (needs to loop)

Edited by dropfaith
notes added to code
Link to comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

When you join the champions table to the synergy table the contents of the single champions record (all those huge text fields) are going to be duplicated in every query output row. (One reason for not using select star is to avoid pulling in masses of non-required data)

One has to question why they are text fileds. EG does the name field really require 65000 characters? And does your id really require 125 digits? (Max int is only 11 digits)

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.