Link to home
Start Free TrialLog in
Avatar of tlasoya808
tlasoya808Flag for United States of America

asked on

Edit Link Created By SQL Query

Aloha,

I am being asked to change a script created URL to a custom URL. We have a php script that builds our navigation menu through a SQL call. Here is that script:

require_once('includes/config.inc');
$htmlnav ='';
$htmlnav.='<div id="left">
    	<div id="nav">
		<ul id="first">';
		
$sql = "SELECT * FROM tbl_main_links ORDER BY fld_main_link_id";
  #### Query 1 MAIN LINKS ####
                    $sql = "SELECT * 
                    		FROM tbl_main_links ml
                    		WHERE ml.fld_status ='1'
							ORDER BY ml.fld_sort ";
					$result = mysql_query($sql, $db_connection) or die(mysql_error());
					$catArray = array(); 
                    $name     = array();
                    ## fetch an array ##
                    while ($row = mysql_fetch_assoc($result)):
                    
                    ## build the arrays for looping through sub links ##
                    $catArray[] = $row['fld_main_link_id'];
                    $text[]     = $row['fld_text'];
                    $ssL[]      = $row['fld_ss'];
                    $link[]     = 'index.php?id='.$row['fld_main_link_id'].'&ss='.$row['fld_ss'].'&'.$row['fld_link'];//$row['fld_link']
					

					## turn on active link style Michele added this code ##				
					$linkon = $row['fld_link'];				
					$active = explode('=', $linkon);
					$activelink = $active[2];
						if ($activelink == $header)
						{
						$on[] = 'on';
						} else {
						$on[] = ' ';
						}
						
					endwhile;
                     
                    ## Loop through the MIAN LINK PRIMARY KEYS ##
                    foreach ($catArray as $key => $value)
					{
					  #### Show MAIN LINK TEXT and build the LINKS with them ####
					$htmlnav.=' <li><a href="'.$link[$key].'" class="'.$on[$key].'">'.$text[$key].'</a></li>';
					 if($_GET['id']==$value){
					  #### Query 2 Rates ####
					 $sql2 = "	SELECT * 
					 			FROM tbl_sub_links t
							 	WHERE t.fld_parent_id = '$value'
							 	AND t.fld_status ='1'
								ORDER BY t.fld_sub_link_id ";
					$result2 = mysql_query($sql2, $db_connection) or die(mysql_error()); 
					$totalRows = (mysql_num_rows($result2));
					####### ONLY SHOW THE <ul id="second"> FOR IYTEMS WITH SUBLINKS #############						
					    if($totalRows >=1)
						{
                        $htmlnav.='<ul id="second">';
                        }
                       #### Loop through the SUB LINKS and MAKE LINKS FROM them ####
                     while ($row2 = mysql_fetch_assoc($result2)):
                    $htmlnav.='<li><a href="index.php?&id='.$row2['fld_parent_id'].'&'.$row2['fld_link'].'&ss='.$ssL[$key].'">'.$row2['fld_text'].'</a></li>';
                    endwhile;
                    ### ONLY CLOSE IT IF WE NEEDED IT ######
                    if($totalRows >=1)
						{
                    	$htmlnav.=' </ul>';
                    	}
                    }##END the condition
                    ## End the loop through the NAV LINKS ###
             	}
             	$htmlnav.='
             	</ul>
        <img src="images/nav_bottom.jpg" />
		</div>
		<object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=6,0,0,0" width="200" height="800">
 		<param name="movie" value="images/MOC.swf" /> 
		<param name="quality" value="high" /> 
		<param name="bgcolor" value="#0C7CB0" /> 
		<embed src="images/MOC.swf" quality="high" bgcolor="#0C7CB0"  width="200" height="800" type="application/x-shockwave-flash" pluginspage="http://www.macromedia.com/go/getflashplayer"></embed>
		</object>
		</div>';

Open in new window


It outputs a URL list that looks like this:

http://mysite.com/index.php?id=1&ss=0&page=home&content=home
http://mysite.com/index.php?id=2&ss=0&page=visitor&content=visitor
http://mysite.com/index.php?id=3&ss=0&page=tickets&content=tickets
http://mysite.com/index.php?id=4&ss=0&page=shop&content=shop
http://mysite.com/index.php?id=5&ss=0&page=dine&content=dine

I want to replace the output of (for example) id 4 and change the URL to just something like 'http://www.someothersite.com'

Being a PHP newbie, I am not sure where to start. Any direction would be greatly appreciated.

This script is being called by index.php when the site is loaded, if that helps.

Mahalo in advance,

Tony

ed. note: Put the code into the code snippet ~Ray
Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

I think it actually outputs "index.php?id=1&ss=0&page=home&content=home".  To substitute a different link, you need to detect the 'id' you want to replace and replace the

$htmlnav.='<li><a href="index.php?&id='.$row2['fld_parent_id'].'&'.$row2['fld_link'].'&ss='.$ssL[$key].'">'.$row2['fld_text'].'</a></li>';

statement with the URL you want to use.
Avatar of tlasoya808

ASKER

You are correct. But it inserts http://www.mysite.com in front of the index.php? url.

I just want a simple link to http://somesite.com.

Is there a statement that I can add to the end of the loop that does:

If id=4 then replace url for id4 with http://somesite.com

Which is what I think you are stating. Just not sure where to put it.

For example:

If ['ml.fld_sort'] = 4
 $htmlnav='<li><a href="http://mysite.com></a></l>

I tried that, placing it in front of:

$htmlnav.='
                   </ul>

And it didn't work.

Thanks for your assistance!
The browser inserts the domain when it requests the page.  I think you will see that the domain is not listed when you view the source of the web page.

 $htmlnav='<li><a href="http://mysite.com></a></l> is missing the last 'i' and the dot '.' after "$htmlnav.=".  And in the code you posted above, $row2['fld_parent_id'] is the id you should be looking for.
I think what you want is a "fully qualified URL," including the http://www... part.  Have a look at line 23 in the snippet above:

$link[] = 'index.php?id='.$row['fld_main_link_id'].'&ss='.$row['fld_ss'].'&'.$row['fld_link'];//$row['fld_link']

Probably that should be something like this:

$link[] = 'http://www.someothersite.com/index.php?id=...
Right :)

Well, the corrected code:

If ['fld_parent_id'] = 4
 $htmlnav.='<li><a href="http://mysite.com></a></li>


Throws this error condition:

Parse error: syntax error, unexpected '[', expecting '(' in /home/moc/public_html/nav.php on line 71

Line 71 is the first line of code.

Google is little help (always go there first :) Ideas?

Thanks!
I see what you are saying, Ray. But this would be just for one of the options, not all of them. I'd like to just do away with SQL script (the urls never change anymore) and just hard code the links.
It should be...

if($row2['fld_parent_id'] == 4)

When you get this working and you want to hard code them, you can just put them in an array and 'foreach' thru them.
Thank you. It didn't throw an error this time :)

Now, as to placement. I put it where I referenced before (prior to line 70 in the above example). It had no effect.

Ideas on where to place it?

(this whole experience is telling me I need to get a handle on php :)
ASKER CERTIFIED SOLUTION
Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for that link. That will be my next step once this is working.

I put the code where you suggested and it had no effect. I am wondering if something shouldn't go in where the main links are built starting at line 40? Where we are looking is where the sublinks go.
Not if it is creating these links:

http://mysite.com/index.php?id=1&ss=0&page=home&content=home

There is only one line that creates those links... actually, I can't find any line in your code that could create those links.  None of them has all the variables, id, ss, page, and content.
That url shows up in line 23. Will play around where I *think* it may go...
Do you think this PHP code in index.php could be causing some grief?

<? 
$header =(isset($_GET['page']) AND ($_GET['page']!=''))? $_GET['page']: 'home';
$content=(isset($_GET['content']) AND ($_GET['content']!=''))? $_GET['content']: 'home';
$_GET['ss']=(isset($_GET['ss']) AND ($_GET['ss']!='') AND (is_numeric($_GET['ss'])) )? $_GET['ss']: '0';
include('nav.php');
$ID = (isset($_GET['id']) AND ($_GET['id']!='')  AND (is_numeric($_GET['id'])) )? $_GET['id']: '1';
$sql = "SELECT fld_title, fld_keywords, fld_description 
        FROM tbl_main_links 
        WHERE fld_main_link_id = $ID";
		$result = mysql_query($sql, $db_connection) or die(mysql_error());
		$row = mysql_fetch_assoc($result);
?>

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Aloha Ray,

I will attempt to do what you suggested.

For the record, I inherited this site, written back in the late 90's. I was (in a previous life) a network engineer, so there is SOME logic in this brain.

Thanks for hanging in there with me.
late 90's
That's about when I started writing PHP, in the PHP3 days.  It's changed a lot since then, and most of the code I wrote a decade or more ago needs to be refactored.  I'm sure this needs refactoring, too.
Agreed! And thanks for the link to the book (and for Dave's PHP link). I am going to call it a night and take this back up tomorrow. If I don't get anywhere (and it looks like refactoring, or just a new site) is the answer, I will split the points between the two of you.

Aloha.
That link is an article written by Ray...
I actually have enough points to orbit Saturn, but thanks for your kind words :-)
Happy New Year 2014, and thanks for using EE, ~Ray
Same to you, Ray. Sorry for the delay in closing out the question.

Aloha!