www.webdeveloper.com
Results 1 to 7 of 7

Thread: [RESOLVED] Displaying results from multiple field query

  1. #1
    Join Date
    Oct 2006
    Location
    Ables Springs, Texas
    Posts
    139

    resolved [RESOLVED] Displaying results from multiple field query

    I am compiling a database of affiliate programs I've joined. Many of those programs have not only their own in-house affiliate program, they also are listed with several affiliate networks. My data entry form and database have fields for those networks as follows:
    network1
    networkurl1
    network2
    networkurl2
    network3
    networkurl3

    On my search form, I wish to search the database for programs by affiliate network to generate a list of programs in that network. The search query seems to be working okay, but I can't seem to get the display right.
    Code:
    $query = "SELECT * FROM programs WHERE network1 LIKE '$search' OR network2 LIKE '$search' OR network3 LIKE '$search' ORDER BY progname ASC";
    I entered test data for affiliate programs and the corresponding affiliate networks as follows:
    Program Name = affprog1
    network1 = ABC
    networkurl1 = http://www.abc.com/?aff=jg
    network2 = DEF
    networkurl2 = http://www.def.com/?aff=jg
    network3 = XYZ
    networkurl3 = http://www.xyz.com/?aff=jg

    Program Name = affprog2
    network1 = XYZ
    networkurl1 = http://www.xyz.com/?aff=pu
    network2 = DEF
    networkurl2 = http://www.def.com/?aff=pu

    A search for programs belonging to XYZ lists both program names, but I can't get it to list the corresponding network name and network referral url. I've tried several variations, including:
    Code:
    <?
    while ($i < $num) {
    $progname=mysql_result($result,$i,"progname");
    $network1=mysql_result($result,$i,"network1");
    $networkurl1=mysql_result($result,$i,"networkurl1");
    $network2=mysql_result($result,$i,"network2");
    $networkurl2=mysql_result($result,$i,"networkurl2");
    $network3=mysql_result($result,$i,"network3");
    $networkurl2=mysql_result($result,$i,"networkurl3");
    ?>
            <tr>
              <td><? echo $progname; ?></td>
              <td><? echo $network1 ; ?></td>
              <td><a href="<? echo $networkurl1; ?>"><? echo $networkurl1; ?></a></td>
              <td><? echo $network2 ; ?></td>
              <td><a href="<? echo $networkurl2; ?>"><? echo $networkurl2; ?></a></td>
              <td><? echo $network3 ; ?></td>
              <td><a href="<? echo $networkurl3; ?>"><? echo $networkurl3; ?></a></td>
            </tr>

  2. #2
    Join Date
    May 2010
    Location
    Effingham, IL
    Posts
    87

    mysql_fetch_array

    Here is an easy way to get everything you need in just a few lines of code.

    $sql = "select * from table"; //your sql statement
    $query = mysql_query($sql, $connection);
    $array = mysql_fetch_assoc($query);

    to display the field from the database simply use the following

    echo $array['field1'];

    This is easier then having to build several variables all the time.
    Ryan Condron
    Freelance Web Developer
    http://www.rebelwebdevelopment.com

  3. #3
    Join Date
    Oct 2006
    Location
    Ables Springs, Texas
    Posts
    139
    Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

  4. #4
    Join Date
    May 2010
    Location
    Effingham, IL
    Posts
    87

    Mysql

    I have used mysqli for so long I need to look up the reference on the orginal mysql functions. Just an FYI the difference between is that mysql is procedural and mysqli is object oriented. You can use mysqli in procedure code, the mysqli is just updated version of mysql.

    Try this

    <?PHP
    $con = mysqli_connect($host, $user, $password, $database);
    $sql = //mysql query
    $query = mysqli_query($con, $sql);
    while ($row = mysqli_fetch_array) {
    echo $row['field1'];
    }
    ?>

    This will let you have a result of multiple rows. The functional is the same for mysql functions you will need to look up the reference. I just know that the way you had it coded above was lengthy and I was trying to shorten it up for you. Also, if you are just starting out in PHP, I would checkout the Head First book on PHP. I learned the basics from the book and have continued to learn new things.
    Ryan Condron
    Freelance Web Developer
    http://www.rebelwebdevelopment.com

  5. #5
    Join Date
    Oct 2006
    Location
    Ables Springs, Texas
    Posts
    139
    I don't understand how to implement what you are talking about.

    Here is the entire page code I have so far, to better explain what I am attempting to accomplish.

    Code:
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml" xmlns:v="urn:schemas-microsoft-com:vml" >
    <head>
      <meta name="robots" content="noindex, nofollow"/>
    	<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    	<link rel="stylesheet" type="text/css" href="http://www.jwgmg.com/styles/style2.css"/>
      <title>Search Results for Affiliate Program Database</title>
    	<base target="_blank" />
    </head> 
    <body>
    <div style="width: 100%; padding: 1em; text-align: center; margin: auto">
      <p class="center bold lg em">Program by Network Search Results</p>
    <? 
    include("dbinfo.inc.php");
    
    $search="".$search.""; 
    
    $query = "SELECT * FROM programs WHERE netname1 LIKE '$search' OR netname2 LIKE '$search' OR netname3 LIKE '$search' ORDER BY progname ASC";
    $result=mysql_query($query)or die(mysql_error()); 
    
    $num=mysql_numrows($result);
    $i=0;
     
    if($netname1=="$search"){$neturl=="$netnameurl1";}
    elseif($netname2=="$search"){$neturl=="$netnameurl2";}
    elseif($netname3=="$search"){$neturl=="$netnameurl3";}
    
    mysql_close();
    
    ?>
    	<h1><? echo $search; ?></h1>
    	<table style="border: 1px solid black; text-align: center; margin: auto">
    		<thead style="background-color: #369; color: white">
    		<tr>
    			<th>Program Name</th>
    			<th>Referral URL</th>
    		</tr>
    		</thead>
    <?	
    while ($i < $num) {
    $progname=mysql_result($result,$i,"progname");
    ?>
    
    		<tbody class="left">
    		<tr>
    		  <td><? echo $progname; ?></td>
    		  <td><a href="<? echo $neturl; ?>"><? echo $neturl; ?></a></td>
    		</tr>
        
    <?
    ++$i;
    } 
    ?>
    		</tbody>        
      </table>
      <table style="margin-left: 12em; border: 2px solid red">
      	<tr><td style="background-color: #369; color: white"><? echo $num; ?></td></tr>
      </table>
    </div>	 
    </body>
    </html>

  6. #6
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,174
    Get rid of the mysql_close(), or else move it to after you are done retrieving all the result rows. (The connection is automatically closed when the script finishes, so normally you don't need to close it explicitly like that unless you expect to have a fair amount of processing in your script once you are done with the connection.)
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  7. #7
    Join Date
    Oct 2006
    Location
    Ables Springs, Texas
    Posts
    139
    Here's the scenario. I have numerous affiliate programs I promote through affiliate networks such as Commission Junction, Cleverbridge and LinkShare. Some of those affiliate programs list with more than one affiliate network.

    I list the individual program in my database as "progname" and the associated affiliate network(s) as "netname1", "netname2" and "netname3" with corresponding referral urls for the associated affiliate program as "netnameurl1", "netnameurl2" and "netnameurl3."

    My desired output is a list of affiliate programs and their matching referral url where the affiliate network name matches the search criteria.

    For example, let's say affiliate program "WebDeveloper.com" is listed with both Commission Junction and LinkShare and my search term is Commission Junction. LinkShare is entered as "netname1" and Commission Junction is entered as "netname2." My desired output would be:

    WebDeveloper.com http://www.cj.com/?id=jwg

    If my search term was LinkShare, the desired output would be:

    WebDeveloper.com http://www.ls.com/?id=gra

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles