mallen

Ok getting closer. It shows all the states and countries. But it is listing Alberta Canada, British Columbia, Ontario, Qubec, India, Mexico, Puerto Rico, and United States in that order. Now I want to list like this. Or atleast United States on top: United States: Then all the states Canada: India: Mexico: Puerto Rico <?php $sql= "SELECT cty.id, cty.name, states.id, states.state_name, `agent`.`company_name`, `agent`.`address` , `agent`.`city` , `agent`.`zip` , `agent`.`phone` , `agent`.`fax` , `agent`.`email` , `agent`.`website` , `agent`.`territory` , `agent`.`contact_name`, `agent`.`country`, `agent`.`state` FROM wp_find_agent AS agent JOIN wp_agent_locations AS locs ON agent.id = locs.agent_id LEFT JOIN wp_agent_states AS states ON states.id = locs.state_id LEFT JOIN wp_agent_countries AS cty ON locs.country_id = cty.id ORDER BY cty.name, states.state_name ASC"; $res = $wpdb->get_results($sql, ARRAY_A); $prev_state = ''; $prev_country = ''; foreach($res as $r){ if ($prev_state != $r['state_name']) { // Print the state echo "<h5>". $r['state_name'] . "</h5>"; $prev_state = $r['state_name']; } if ($prev_country != $r['name']) { // Print the country echo "<h5>". $r['name'] . "</h5>"; $prev_country = $r['name']; } echo"<p class='agentAddress' style='margin-top:0px;'><strong>{$r['company_name']}</strong><br/>\n"; echo $r['address'] ."<br/>\n"; echo $r['city'] . ", ". countryOrState($r['state'], $c['country']) . " " . $r['zip'] . "<br/>\n"; echo "Phone: ". $r['phone'] . "<br/>\n"; echo "Fax: ". $r['fax'] . "<br/>\n"; echo "Email: <a href='mailto:" . $r['email'] . "' target='_blank'>". $r['contact_name'] ."</a><br/>\n"; echo "Website: <a href='http://{$r['website']}' target='_blank'>{$r['website']}</a><br/>\n"; echo "Territory: ". $r['territory'] ."<br/><br/>\n</p>"; } } function countryOrState($r, $c) { global $wpdb; if($r !== "0") return $wpdb->get_var("SELECT `state_name` FROM wp_agent_states WHERE `id`='$r'"); else return $wpdb->get_var("SELECT `name` FROM wp_agent_countries WHERE `id` ='$c'"); } ?>

I get Canada and United States listed. Only Arkasas title shows as a title. I get all the listings just not a title for other states. Ans Puerto Rico, Mexico and India do not show a a listing. $sql= "SELECT cty.id, cty.name, states.id, states.state_name, `agent`.`company_name`, `agent`.`address` , `agent`.`city` , `agent`.`zip` , `agent`.`phone` , `agent`.`fax` , `agent`.`email` , `agent`.`website` , `agent`.`territory` , `agent`.`contact_name`, `agent`.`country`, `agent`.`state` FROM wp_find_agent AS agent JOIN wp_agent_locations AS locs ON agent.id = locs.agent_id LEFT JOIN wp_agent_states AS states ON states.id = locs.state_id LEFT JOIN wp_agent_countries AS cty ON locs.country_id = cty.id WHERE cty.has_state = 1 ORDER BY cty.name, states.state_name ASC"; $res = $wpdb->get_results($sql, ARRAY_A); $prev_state = ''; foreach($res as $r){ if ($prev_country != $r['name']) { // Print the country $prev_country = $r['name']; echo "<h5>". $r['name'] . "</h5>"; if ($prev_state != $r['state_name']) { // Print the state $prev_state = $r['state_name']; echo "<h5>". $r['state_name'] . "</h5>"; } } echo"<p class='agentAddress' style='margin-top:0px;'><strong>{$r['company_name']}</strong><br/>\n"; echo $r['address'] ."<br/>\n"; echo $r['city'] . ", ". countryOrState($r['state'], $r['country']) . " " . $r['zip'] . "<br/>\n"; echo "Phone: ". $r['phone'] . "<br/>\n"; echo "Fax: ". $r['fax'] . "<br/>\n"; echo "Email: <a href='mailto:" . $r['email'] . "' target='_blank'>". $r['contact_name'] ."</a><br/>\n"; echo "Website: <a href='http://{$r['website']}' target='_blank'>{$r['website']}</a><br/>\n"; echo "Territory: ". $r['territory'] ."<br/><br/>\n</p>"; } } //var_dump($sql); function countryOrState($r, $c) { global $wpdb; if($r !== "0") return $wpdb->get_var("SELECT `state_name` FROM wp_agent_states WHERE `id`='$r'"); else return $wpdb->get_var("SELECT `name` FROM wp_agent_countries WHERE `id` ='$c'"); } ?>

Yes after posting that I realized that I removed that Where clause and that didn't work.

Great now I know what those are called. I know they weren't quotes becuase I see it on my keyboard just didn't know what to call them. Continiring on from what Mikosiko was helping me with. I was able to build on what Mikosiko showed me to display the state once. Now I got it to display the country. The old version of my code I was able to display Mexico, Puerto Rico and India. It is not displaying these countries which have a value of "0" in the has_state. I have this function at the bottom. $sql= "SELECT cty.id, cty.name, states.id, states.state_name, `agent`.`company_name`, `agent`.`address` , `agent`.`city` , `agent`.`zip` , `agent`.`phone` , `agent`.`fax` , `agent`.`email` , `agent`.`website` , `agent`.`territory` , `agent`.`contact_name`, `agent`.`country`, `agent`.`state` FROM wp_find_agent AS agent JOIN wp_agent_locations AS locs ON agent.id = locs.agent_id JOIN wp_agent_states AS states ON states.id = locs.state_id JOIN wp_agent_countries AS cty ON locs.country_id = cty.id WHERE cty.has_state = 1 ORDER BY cty.name, states.state_name ASC"; $res = $wpdb->get_results($sql, ARRAY_A); $prev_state = ''; foreach($res as $r){ if ($prev_country != $r['name']) { // Print the country $prev_country = $r['name']; echo "<h5>". $r['name'] . "</h5>"; if ($prev_state != $r['state_name']) { // Print the state $prev_state = $r['state_name']; echo "<h5>". $r['state_name'] . "</h5>"; } } echo"<p class='agentAddress' style='margin-top:0px;'><strong>{$r['company_name']}</strong><br/>\n"; echo $r['address'] ."<br/>\n"; echo $r['city'] . ", ". countryOrState($r['state'], $r['country']) . " " . $r['zip'] . "<br/>\n"; echo "Phone: ". $r['phone'] . "<br/>\n"; echo "Fax: ". $r['fax'] . "<br/>\n"; echo "Email: <a href='mailto:" . $r['email'] . "' target='_blank'>". $r['contact_name'] ."</a><br/>\n"; echo "Website: <a href='http://{$r['website']}' target='_blank'>{$r['website']}</a><br/>\n"; echo "Territory: ". $r['territory'] ."<br/><br/>\n</p>"; } } function countryOrState($s, $c) { global $wpdb; if($r !== "0") return $wpdb->get_var("SELECT `state_name` FROM wp_agent_states WHERE `id`='$s'"); else return $wpdb->get_var("SELECT `name` FROM wp_agent_countries WHERE `id` ='$c'"); } ?>

$sql="SELECT cty.id,cty.name,states.id, states.state_name,`agent`.`company_name`, Sorry I am just not knowledgeable about complex SQL statements. Why does agent need to be in single quotes and the others do not?

Thanks that worked! At first I was not sure where to place inside my code. Also was duplicating the state name because I had an echo statement in there twice. Now next week I will try to get it to display The country, such as United States, then list of states, then Mexico, listing under Mexico like I had it. Can you explain this ? states.id, states.state_name, Why does 'states.id' have to be used instead of state.id

Sorry I didn't think of doing it that way. Put it into PHPMyAdmin and corrected a few typos and it worked. Now I just need to figure how to list a state only once if there are multiple listings for a state. $sql= "SELECT cty.id, cty.name, states.id, states.state_name, `agent`.`company_name`, `agent`.`address` , `agent`.`city` , `agent`.`zip` , `agent`.`phone` , `agent`.`fax` , `agent`.`email` , `agent`.`website` , `agent`.`territory` , `agent`.`contact_name`, `agent`.`country`, `agent`.`state` FROM wp_find_agent AS agent JOIN wp_agent_locations AS locs ON agent.id = locs.agent_id JOIN wp_agent_states AS states ON states.id = locs.state_id JOIN wp_agent_countries AS cty ON locs.country_id = cty.id WHERE cty.has_state = 1 ORDER BY cty.name, states.state_name ASC"; $res = $wpdb->get_results($sql, ARRAY_A); foreach($res as $r){ echo "<h5>". $r['state_name'] . "</h5>"; echo"<p class='agentAddress' style='margin-top:0px;'><strong>{$r['company_name']}</strong><br/>\n"; echo $r['address'] ."<br/>\n"; echo $r['city'] . ", ". countryOrState($r['state'], $r['country']) . " " . $r['zip'] . "<br/>\n"; echo "Phone: ". $r['phone'] . "<br/>\n"; echo "Fax: ". $r['fax'] . "<br/>\n"; echo "Email: <a href='mailto:" . $r['email'] . "' target='_blank'>". $r['contact_name'] ."</a><br/>\n"; echo "Website: <a href='http://{$r['website']}' target='_blank'>{$r['website']}</a><br/>\n"; echo "Territory: ". $r['territory'] ."<br/><br/>\n</p>"; } }

Thisis what I have. Its not displaying any results or errors. ini_set("display_errors", 1); error_reporting(-1); function displayFrontAgents(){ ?> <div id='agentLocation'> <?php $sql="SELECT cty.id, cty.name, states.id, states.state_name, `agent`.`company_name`, `agent`.`address` , `agent`.`city` , `agent`.`zip` , `agent`.`phone` , `agent`.`fax` , `agent`.`email` , `agent`.`website` , `agent`.`territory` , `agent`.`contact_name`, `agent`.`country`, `agent`.`state` FROM wp_find_agent AS agent JOIN wp_agent_locations AS locs ON agent.id = locs.agent_id JOIN wp_agent_states AS states ON states.id = locs.state_id JOIN wp_agent_countires AS cty ON locs.country_id = cty.id WHERE cty.has_state = 1 ORDER BY cty.name, states.state_name ASC"; $res = $wpdb->get_results($sql, ARRAY_A); foreach($res as $r){ echo "<h5>". $r['states.state_name'] . "</h5>"; echo"<p class='agentAddress' style='margin-top:0px;'><strong>{$r['company_name']}</strong><br/>\n"; echo $r['address'] ."<br/>\n"; echo $r['city'] . ", ". countryOrState($r['state'], $r['country']) . " " . $r['zip'] . "<br/>\n"; echo "Phone: ". $r['phone'] . "<br/>\n"; echo "Fax: ". $r['fax'] . "<br/>\n"; echo "Email: <a href='mailto:" . $a['email'] . "' target='_blank'>". $a['contact_name'] ."</a><br/>\n"; echo "Website: <a href='http://{$a['website']}' target='_blank'>{$a['website']}</a><br/>\n"; echo "Territory: ". $a['territory'] ."<br/><br/>\n</p>"; } }

I put the display errors in, but of course there were no errors displayed. I think the person before me who wrote this set it up so it would display the name of the state onnce, then display all listings below that. Becuase I tried changing it around and it would list a state name each time. I could query the find_agent table but displaying the state name with multiple listings is the issue. This table includes id, company_name, address, state(numeral) ect.....

I have this code that is listing names of states with agent, company, address ect. But i don't want to list a state that doesn't have any agent associated to it. Looking at what I have here can someone point me in the right direction how to achieve this? I am thinking right after the line #12 foreach($states as $st create a statment that only will print if there is data. The trouble is everything I have tried returns true becuase every thing has a value. "Agent locations" table includes locs_id, agent_id, state_id and country_id. I need somethign that says find this data if it finds all of these only. $sql = "SELECT DISTINCT(cty.name), cty.has_state, cty.id FROM agent_countires AS cty LEFT JOIN agent_locations AS ag ON ag.country_id= cty.id"; $res = $wpdb->get_results($sql, ARRAY_A); foreach($res as $r){ ?> <h3><?php echo $r['name'];?></h3> <?php if($r['has_state'] == "1") : $states = $wpdb->get_results("SELECT `id`,`state_name` FROM wp_agent_states WHERE `country`='{$r['id']}' ORDER BY state_name ASC", ARRAY_A); foreach($states as $st){ //need statement here if state doesn't have anything associated done print, or else ... echo "<h5>". $st['state_name'] . "</h5>"; //<<<<It prints state names that don't have any results $newQuery = "SELECT `agent`.`company_name` , `agent`.`address` , `agent`.`city` , `agent`.`zip` , `agent`.`phone` , `agent`.`fax` , `agent`.`email` , `agent`.`website` , `agent`.`territory` , `agent`.`contact_name`, `agent`.`country`, `agent`.`state` FROM wp_find_agent AS agent LEFT JOIN agent_locations AS locs ON agent.id = locs.agent_id WHERE locs.state_id ='". $st['id'] ."'"; $newLocs = $wpdb->get_results($newQuery, ARRAY_A); if(count($newLocs) > 0) : foreach($newLocs as $a) { echo"<p class='agentAddress' style='margin-top:0px;'><strong>{$a['company_name']}</strong><br/>\n"; echo $a['address'] ."<br/>\n"; echo $a['city'] . ", ". countryOrState($a['state'], $a['country']) . " " . $a['zip'] . "<br/>\n"; echo "Phone: ". $a['phone'] . "<br/>\n";

I was able to get it to work. The solution is: <?php $pageinfo = file_get_contents('http://www.mysite.com/file.php'); echo $pageinfo;

I have a PHP page that contains a large mixture of PHP and HTML. I am working on a mobile theme for a website and I am triying to figure a way to load this section of code rather than using "echo" for each line. If there a way to do something like: include( this php page....) echo " mypage.php";

Krakjoe, Thanks! I used a little different format than you with $output rather than printf(). But it is working. Can you explain these two lines? $literature as $col => $lit (($col % 3) == 0)

Ok I moved that </tr> and </table> outside the loop. now I get one row of three and the rest are are on a second row. function displayLiterature() { global $wpdb; define('COLS', 3); // number of columns $col = 0; // number of the last column filled $output = ""; $query = "SELECT * FROM literature ORDER By lit_id DESC"; $literature = $wpdb->get_results($query, ARRAY_A); echo '<table>'; echo '<tr>'; if(count($literature) > 0) : foreach($literature as $lit) { $col++; $output .= "<td>"; $output .= "<img src='...{$lit['lit_image']}' width='130' height='170' alt='thumbnail'/>"; $output .= "<a href='http:...../{$lit['lit_pdf']}'>" . $lit['lit_title'] ."</a>"; $output .= "</td>"; {if ($col == COLS) // have filled the last row $output .= "</tr>"; // start a new one } } $output .= "</tr>"; // end last row $output .= "</table>"; else: $output .= "<p>Sorry no results</p>"; endif; echo $output; //var_dump($lit); }

I am trying to get my data to output and display in a 3 column table. I can't figure out the loop becuase it keeps outputing </tr></table> after every image. function displayLiterature() { global $wpdb; define('COLS', 3); // number of columns $col = 0; // number of the last column filled $output = ""; $query = "SELECT * FROM literature ORDER By lit_id DESC"; $literature = $wpdb->get_results($query, ARRAY_A); echo '<table>'; if(count($literature) > 0) : foreach($literature as $lit) { $col++; $output .= "<td>"; $output .= "<img src='".....{$lit['lit_image']}' width='130' height='170' alt='thumbnail'/>"; $output .= "<a href='........{$lit['lit_pdf']}'>" . $lit['lit_title'] ."</a>"; {if ($col == COLS) // have filled the last row $output .= "</tr><tr>"; // start a new one } $output .= "</tr>"; // end last row $output .= "</table>"; } else: $output .= "<p>Sorry no results</p>"; endif; echo $output; }

It's working now. I see the issue was '% ".$searchQuery ." % and changed it to '$searchQuery' $src="SELECT cat.cat_name, cat.cat_id, prod.prod_id, prod.prod_name, prod.prod_description,prod.prod_mainImage, prod.prod_model, catalog, prod_active FROM `products` as prod LEFT JOIN `category_assoc` AS assoc ON assoc.prod_id = prod.prod_id LEFT JOIN `categories` AS cat ON cat.cat_id = assoc.cat_id WHERE prod.prod_name LIKE '$searchQuery' OR prod.prod_description LIKE '$searchQuery' OR prod.prod_model LIKE '$searchQuery' AND prod.prod_active = '1' ORDER BY prod_model ASC " ;

Thanks. I echoed the sql sentence and looked for errors but all I am doing is printing the line I see already typed. The error in Phpmyadmin just quotes the beginning of the entire statement and not the specific error. Can you give me a hint as to my error you see? Is it a typo, a space? The order?

Yes I did an echo on the search and it displays the search entered. I tried the SQL in PHPMyadmin and I get #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version. What I mean by it does not work, it returns "Your search returned no results. Please try refining your search" $searchQuery = $_REQUEST['query']; $src = "SELECT cat.cat_name, cat.cat_id, prod.prod_id, prod.prod_name, prod.prod_mainImage, prod.prod_model, prod.catalog FROM `myproducts` as prod LEFT JOIN category_assoc AS assoc ON assoc.prod_id = prod.prod_id LEFT JOIN `categories` AS cat ON cat.cat_id = assoc.cat_id WHERE LOWER (prod.prod_name) LIKE '% ".$searchQuery ." %' OR LOWER (prod.prod_description) LIKE '% ".$searchQuery ."%' OR LOWER(prod.prod_model) LIKE '% ".$searchQuery ."%') AND prod.prod_active = '1' ORDER BY prod.prod_model ASC"; echo $searchQuery; $results = $wpdb->get_results($src, ARRAY_A); if(count($results) > 0) { foreach($results as $res) { <div class="productRow"> <div class='thumbs'> <img src='<?php echo $res['prod.prod_mainImage'];?>' alt='MainThumbnail'/></div> <h2><a href='?page_id=26&singleProduct=<?php echo $res['prod.prod_id'];?>'><?php echo $res['prod.prod_name']; ?></a></h2><br /> <p style='margin:2px; 0 15px;'><strong>Product Code :</strong> <?php echo $res['prod.prod_model'];?></p><br /> <div class='click'> <a href='?page=<?php echo $res['prod.prod_id'];?>'>Click to view</a> </div></div> <?php } } else echo "<p>Your search returned no results. Please try refining your search</p>\n"; else: echo "<p>Please enter a query in the search box at the top of the page</p>\n"; endif; ?>

Is there anything here in my code that could cause my search not to work. I checked and double checked my table names are correct. I tried removing the ` but didn't work. $searchQuery = $_REQUEST['query']; $src = "SELECT cat.cat_name, cat.cat_id, prod.prod_id, prod.prod_name, prod.prod_mainImage, prod.prod_model, prod.catalog FROM `myproducts` as prod LEFT JOIN category_assoc AS assoc ON assoc.prod_id = prod.prod_id LEFT JOIN `categories` AS cat ON cat.cat_id = assoc.cat_id WHERE LOWER (prod.prod_name) LIKE '% ".$searchQuery ." %' OR LOWER (prod.prod_description) LIKE '% ".$searchQuery ."%' OR LOWER(prod.prod_model) LIKE '% ".$searchQuery ."%') AND prod.prod_active = '1' ORDER BY prod.prod_model ASC";

Solved it. if ( ! empty($documents) ) foreach($documents as $addFile) { $addFile = ltrim($addFile);// <--- added here if(strpos($addFile, ".doc") !== false) $addFileType = "DOC"; else $addFileType = "PDF";

Thanks. I tried what you suggested and it changed all my file types to "PDF" and also didn't strip the spaces. if ( ! empty($documents) ) foreach($documents as $addFile) { if(strpos($addFile, ".doc") !== false) $addFileType = "DOC"; else $addFileType = "PDF"; if ( ! empty($documents) && is_array($documents)) // added foreach($documents as $addFile) { $addFile = ltrim($addFile); } $insertAddFile = "INSERT INTO ". FILES . "(`prod_id`, `file_name`, `file_type`) VALUES('$lastId', '$addFile', '$addFileType')"; $wpdb->query($insertAddFile); }

Premiso, Sorry I should have been more clear. This part of my code works. There is a addFile and also a addFileTYPE, The addFile is the part that needs the spaces trimmed. if($data[11] == "" || empty($data[11])) $documents = ""; else $documents = explode(",", $data[11]); if ( ! empty($documents) ) foreach($documents as $addFile) { if(strpos($addFile, ".doc") !== false) $addFileType = "DOC"; else $addFileType = "PDF"; $insertAddFile = "INSERT INTO " FILES . "(`prod_id`, `file_name`, `file_type`) VALUES('$lastId', '$addFile', '$addFileType')"; $wpdb->query($insertAddFile);

* Please only comment if you are willing and able to help. I posted the code I tried from premiso. I will post it again. $documents = explode(",", $data[11]); array_walk($documents, 'ltrim');

Thanks Premiso for trying to help me out with this. Yes I now what I want to do just not how to. I need to trim any blank or empty space that may appear before a document's name. I tried that and got this error: Warning: array_walk() expects parameter 1 to be array, string given in

I have tried both of these. $documents = ltrim(explode(",", $data[11])); $documents = explode(",", ltrim($data[11]));