7 Replies Latest reply: Jan 7, 2014 1:14 AM by osgood_ RSS

    How to provide a link from a resultset variable?

    whatalotofrubbish Community Member
    whatalotofrubbish Jan 5, 2014 2:47 PM

      I have a list of id and name pairs in a database table using Mysqli.

      The PHP code that prints them out is as follows:

       

      while( $row = mysqli_fetch_array( $resultset, MYSQLI_NUM))

      { echo $row[0] ." - ". $row[1] ."\n <br/ >";

      }

       

      This works fine. There are up to 6000 items in this list, so it uses a repeat region (not shown).

      I want to link to a page that will use the id to do things with it (like edit or delete a record).

      I need to send the value of $row[0] to the next page, but use $row[1] as a link to a page called testlink.php.

       

      The following code:

       

      <a href="testlink.php?ref=<?php echo $row[0]; ?>"><?php echo $row[1];?> </a>

       

      seems like a way to do it, but I cannot figure just how to insert this into the echo line without getting errors.

       

      Can anyone help with the php code to do this?

       

      And is there a better way to access individual items in very large lists other than using what is in effect a multi-page drop down menu.

       

      Howard Walker

        • 1. Re: How to provide a link from a resultset variable?
          osgood_ CommunityMVP
          osgood_ Jan 6, 2014 2:52 AM (in response to whatalotofrubbish)

          Not really following.

           

          Are you trying to return the names and ids in a list and create a link to a delete or edit page?

           

          if so I would just query the database like so:

           

           

          <?php

           

          $conn = new mysqli('localhost' , 'username' , 'password' , 'databaseName');

           

          $getNameList = $conn->("SELECT * FROM tablename") or die ($conn->error);

           

          <?php

           

           

          <?php while ($row = $getNameList->fetch_assoc()) { ?>

           

          <p><a href="textlink.php?ref="<?php echo $row['id']; ?>"><?php echo $row['name']; ?></a></p>

           

          <?php } ? >

          • 2. Re: How to provide a link from a resultset variable?
            bregent CommunityMVP
            bregent Jan 6, 2014 9:48 AM (in response to whatalotofrubbish)

            Your idea looks good, probably just a syntax error. I'm not sure but possibly you need whitespace before the last closing php '?>"

             

            <a href="testlink.php?ref=<?php echo $row[0]; ?>"><?php echo $row[1]; ?> </a>

             

            >And is there a better way to access individual items in very large

            >lists other than using what is in effect a multi-page drop down menu.

             

            If at all possible, allow the user to filter the list down to a reasonable number of items; 6000 is too much

            • 3. Re: How to provide a link from a resultset variable?
              whatalotofrubbish Community Member
              whatalotofrubbish Jan 6, 2014 10:28 AM (in response to osgood_)

              Thanks a lot Osgood. It did cause many syntax errors, but it gave me the hint:

              This is my code at the mpoment, and it works as needed.

               

              $conn = mysqli_connect( $DatabaseServer, $DatabaseUser, $DatabasePassword, $DatabaseName);

              $sql ='SELECT id, company FROM companies';

               

              $resultset = mysqli_query( $conn, $sql);

                while ($row = $resultset->fetch_assoc()) { ?>

              <a href="testlink.php?ref="<?php echo $row['id']; ?>"><?php echo $row['company']."\n"."<br>"; }?></a><br />

               

               

              This produces what I need, but the id index is not printed before the linked name.

               

              Why did you use new mysqli instead of just mysqli.?

               

              Howard

              • 4. Re: How to provide a link from a resultset variable?
                bregent CommunityMVP
                bregent Jan 6, 2014 11:13 AM (in response to whatalotofrubbish)

                >This produces what I need, but the id index is not printed before the linked name.

                 

                Do you mean the id is not being inserted into the querystring?

                 

                Looks like you have an extra quote mark in there. Try this

                 

                <a href="testlink.php?ref=<?php echo $row['id']; ?>"><?php echo $row['company']."\n"."<br>"; }?></a><br />

                • 5. Re: How to provide a link from a resultset variable?
                  whatalotofrubbish Community Member
                  whatalotofrubbish Jan 6, 2014 12:29 PM (in response to bregent)

                  The id is now inserted thanks and passed to the next page.

                  This code below actually shows the id in the drop down list thanks to the bold code, but it is also underlined - I can probably fix that with CSS, but I only need it to ensure that the correct id is being passed while testing, so its not worth the trouble.

                   

                  <a href="testlink.php?ref=<?php echo $row['id']; ?>"><?php echo $row['id'] . " ". $row['company']."\n"."<br>"; }?></a><br />

                  • 6. Re: How to provide a link from a resultset variable?
                    whatalotofrubbish Community Member
                    whatalotofrubbish Jan 6, 2014 1:02 PM (in response to bregent)

                    When you have a list of 6000, the only sensible way to filter them down I originally thought was to use the first letter.

                    However, I have now found that if I use mysqli to select all items containing a few (say 4 ) letters, I can usually get the list down to less than 5 items, display a list of these and then just select one of them.

                    See http://tyneships2.co.uk/zebras/word_search.php

                     

                    This site was moved to a version 5.5 server when it was written in mysql and as a result I am having to rewrite it. Its a bit scrappy, but works after a fashion, unlike the original site.

                    This selects and displays all items with the letters in the name fairly quickly, but there are only about 4000 items to look through, with more being added daily.

                    Later I will use the shortlist to pick the final one prior to displaying it.

                    Try entering sal sall and sally. if you have a lot of returned pages, like you have for sal, then depending on your connection speed, you could be waiting ages, but if you use sally, then you have only one and that is instant, even at my slow speed. So like you say, it will be better to filter using word search and then pick the right one.

                    Only problem is that your first search may not find a result.

                    Thanks for your input.

                    • 7. Re: How to provide a link from a resultset variable?
                      osgood_ CommunityMVP
                      osgood_ Jan 7, 2014 1:14 AM (in response to whatalotofrubbish)

                      One tiny error can cause a ripple - an incorrectly closed tag:

                       

                      <?php

                       

                      $conn = new mysqli('localhost' , 'username' , 'password' , 'databaseName');

                       

                      $getNameList = $conn->("SELECT * FROM tablename") or die ($conn->error);

                       

                      <?php ------------- SHOULD have been ?>