Database and PHP (or HTML) hyperlinks [Solved]

Posts
982
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 27, 2018
-
I have found myself with a bit of time on my hands so I'm revisiting a PHP web site I developed some time ago whilst messing about so I thought I would see if I could learn more tricks. I found a spot to play in my script so thought Id try to develop it further ...

Im having trouble finding out how to pull data from my database and display the results as hyperlinks which if the user clicks on they can be sent to another page. But I only want one page to go to which will break down the data a bit more.

What I have is (and I'm sure there is a better way) ...

// get data for Problem Category - eg password or system bug etc
$query = "SELECT problem_category.Problem_Type, COUNT(*) AS qty
FROM problem_category, raw_data
WHERE problem_category.Problem_ID=raw_data.Problem_category AND raw_data.Date BETWEEN '$start1' AND '$end1'
GROUP BY problem_category.Problem_Type
ORDER BY qty DESC ";

$result = mysqli_query($conn, $query);


while($row = mysqli_fetch_array($result))
{

if (in_array('Problem A', $row, true)) {
echo '<a href="mydata-1">Problem A'." - (".$row["qty"].")</a> <br />";
}

if (in_array('Problem B', $row, true)) {
echo '<a href="mydata-2">Problem B'." - (".$row["qty"].")</a> <br />";
}

if (in_array('Problem C', $row, true)) {
echo '<a href="mydata-3">Problem C'." - (".$row["qty"].")</a> <br />";
}
}

What this does is list a set of problem types that occur between 2 dates, and the number of times they occurred - all on separate lines - Like this:

Problem A - (number of times it occurs)
Problem B - (number of times it occurs)
Problem C - (number of times it occurs)

I want to pass the "mydata-(x)" values into another (single) page - in a similar way to how forms work using the "get" or "post" command. When in the new page I will want to manipulate more data but thats not an issue ... yet. But the problem I have is getting into a new page and carrying over the mydata-(x) value. There has to be a better way than creating 3 more pages for the same data manipulation than having several pages doing the same thing.

I hope this makes sense.

There is a virtual beer waiting if anyone can find a better way.
See more 

Your reply

3 replies

Posts
10934
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
December 14, 2018
0
Thank you
Well sir, that is a good start. Just make the links inline with the code, like:

DisplayLine="<a href="""theservername\" & foldername & "\HereIsThePage.asp?article=" & ARTICLENAME(x) & _
"&page=" & ARTICLEPAGE(Z) & """>The link for Requested ( " & ARTICLENAME(x) & ", page " & ARTICLEPAGE(Z) & " is here</a>"


Will produce inline:
<a href="theservername\SomeFOlderName\HereIsThePage.asp?article=FindingLinks&page=3">The link for Requested FINDINGLINKS, page 3 is here</a>

Notice the THREE QUOTES (in order to escape the " you need to wrap the HREF!)

I hope that helps!


BrianGreen
Posts
982
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 27, 2018
-
Thanks ac3mark,

Am I right in thinking that this isnt php.or html? I only know a little of these two languages so anything else is a little outside my scope and understanding.

I have got a little further, but Im sure this isnt right as it doesnt allow me to select the second link with the session being correct - it passes the first session found regardless of the link chosen. I have used sessions (which are declared in both pages) to do this. It looks like this ...

if (in_array('Problem A', $row, true)) {
$_SESSION['ProblemType'] = 'Problem A';
echo '<a href="mydata2.php">Problem A'." - (".$row["qty"].")</a> <br />";
}

if (in_array('Problem B', $row, true)) {
$_SESSION['ProblemType'] = 'Problem B';
echo '<a href="mydata2.php">Problem B'." - (".$row["qty"].")</a> <br />";
}

if (in_array('Problem C', $row, true)) {
$_SESSION['ProblemType'] = 'Problem C';
echo '<a href="mydata2.php">Problem C'." - (".$row["qty"].")</a> <br />";
}

Am I flogging a dead hoarse with this approach?
ac3mark
Posts
10934
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
December 14, 2018
-
Brian, I used the string as an example for what the line would be. I was hoping you would get the example, and use the language that you are using. It looks like you got the idea of what I was saying!
Respond to ac3mark
Posts
982
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 27, 2018
0
Thank you
OMG - I dont believe how easy this was!

Page 1 has this code ...
// get data for Problem Category - eg password or system bug etc
$query = "SELECT problem_category.Problem_Type, COUNT(*) AS qty
FROM problem_category, raw_data
WHERE problem_category.Problem_ID=raw_data.Problem_category AND raw_data.Date BETWEEN '$start1' AND '$end1'
GROUP BY problem_category.Problem_Type
ORDER BY qty DESC ";

$result = mysqli_query($conn, $query);

while($row = mysqli_fetch_array($result))
{

if (in_array('Problem A', $row, true)) {
echo '<a href="mydata.php?A">Problem A'." - (".$row["qty"].")</a> <br />";
}

if (in_array('Problem B', $row, true)) {
echo '<a href="mydata.php?B">Problem B'." - (".$row["qty"].")</a> <br />";
}

if (in_array('Problem C', $row, true)) {
echo '<a href="mydata.php?C">Problem C'." - (".$row["qty"].")</a> <br />";
}


The target page (mydata.php) has this code ...
$ProblemType = $_GET['ProblemType']

Then simply use the $ProblemType variable where it is needed in the script on that page.

Im sure there is a better answer, but this works.

I award myself 2 virtual beers!
Ambucias
Posts
50324
Registration date
Monday February 1, 2010
Last seen
November 22, 2018
-
Virual beers are as dry as the inside of an angel's underwear.
Respond to BrianGreen
Posts
10934
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
December 14, 2018
0
Thank you
Brian, you could in theory, send it back to the original code, and select case the problem. Now, the same page handles all of the control, and you are not bouncing back and forth between pages.

SOmething like:
if request("fix")="yes" then
do something
end if

if request("fix")="maybe" then
do something else
end if

Or better yet:
TheVariable = request("fix")
Select case TheVariable:


Case: Fix
do something

Case: maybe
something else

end case



Then, your query could even drill down to a specific query type. Just saying.

I spent 3 years developing a construction back end for managing projects and materials, and have over 608 ASP pages linked together in this manner.

<%'******copyright 2004
dbsource="\\db\s1.mdb"
response.buffer=true%>
<%dim whnum, fromnum, partnum, Sdesc, strpartnumber
Sdesc=request("desc")
whnum=request("n")
fromnum=request("type")
partnum=request("p")
strpartnumber=request("partnumber")
%>
<%
dim sCWHnum(), sGWHnum(100), sMWHnum(100)
dim sCpart(), sGpart(100), sMpart(100)
dim sCdesc(), sGdesc(100), sMdesc(100)
dim sChand(), sGhand(100), sMhand(100)
dim x,t,u,v,w,y,z
dim AC, AG, AM, AF, AH
dim strcastle



Set CONN = CreateObject("ADODB.CONNECTION")
Set RC = CreateObject("ADODB.recordset")


strcxn="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbsource & ";Persist Security Info=False"

conn.Open strcxn

'######get the part
if whnum <> "" then
strcastle = "SELECT tblinventory.[wh#], tblinventory.[part #], tblinventory.Description FROM tblinventory WHERE (((tblinventory.[wh#])=" & whnum & "));"
end if

if partnum <> "" then
strcastle = "SELECT * from tblinventory WHERE [part #] ='" & partnum & "';"
end if

if Sdesc<>"" then
strcastle = "SELECT [wh#], [part #], description from TBLINVENTORY WHERE [description] LIKE '" & Sdesc & "' ORDER BY [part #];"
end if

if strpartnumber<>"" then
strcastle = "SELECT ID, LOT, cname from TBLCURRENTbath WHERE [cname] LIKE '" & strpartnumber & "';"
end if


RC.open strcastle, CONN
if not RC.EOF then
AC=0
RC.MOVEFIRST
DO WHILE NOT RC.EOF
redim preserve sCWHnum(AC+1)
redim preserve sCPart(AC+1)
redim preserve sCdesc(AC+1)
'redim preserve sChand(AC+1)

sCWHnum(AC)=RC(0)
sCPart(AC)=RC(1)
sCdesc(AC)=RC(2)
'sChand(AC)=RC(3)
AC=AC+1
RC.MOVENEXT
LOOP
else
response.write("<h3><font color=red>No Records Match. <a href=javascript:history.back()>Back</a>")
end if
RC.CLOSE
conn.close
set RC=nothing
set CONN=nothing
%>
<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<title>Parts Lookup</title>
</head>
<body>

<TABLE style="border:none;">
<TR>
<td valign=top><h3>List</h3><a href="javascript:history.back();" onmouseover="window.status='Go Back 1 page in history'; return true" onmouseout="window.status=''; return true">Back</a></td>
<td>
<%if strpartnumber<>"" then%>
<table border=1 cellspacing=0>
<tr><td align=center nowrap></TD><TD nowrap>Part ID#</TD><td nowrap>LOT</TD><td>Part Number (Common Name)</TD></TR>
<%for t = 0 to AC-1%>
<tr><td align=center> </TD><TD>
<a href="changeorder.asp?part=<%=sCWHnum(t)%>&slot=<%=sCPart(t)%>" target=_blank><%=sCWHnum(t)%></a> </TD><TD><%=sCpart(t)%> </TD><TD><font size=2><%=sCdesc(t)%> </TD></TR>
<TR><TD colspan=4><a href="cp.asp?wh=<%=sCWHnum(t)%>">Pick</a></TD></TR>
<%next%>
</table>
<%else%>
<table border=1 cellspacing=0>
<tr><td align=center nowrap>Cost</TD><TD nowrap>WH #</TD><td nowrap>Part #</TD><td>Description</TD></TR>
<TR><TD colspan=4><h3><%=partnum%></H3></td></tr>
<%for t = 0 to AC-1%>
<tr><td align=center> </TD><TD><a href="edit_inventory.asp?Submit=Submit&whnumber=<%=sCWHnum(t)%>"><%=sCWHnum(t)%></a> </TD><TD><%=sCpart(t)%> </TD><TD><font size=2><%=sCdesc(t)%> </TD></TR>
<TR><TD colspan=4><a href="cp.asp?wh=<%=sCWHnum(t)%>">Pick</a></TD></TR>
<%next%>
</table>
<%end if%>
</td>
</tr>
</table>
<p> </p>
</body>
</html>


Respond to ac3mark